﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Math;
using DocumentFormat.OpenXml.Wordprocessing;
using Hangfire;
using Hangfire.LiteDB;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Repository.Extensions;
using housingsafety.server.Svcs;
using KS3;
using KS3.Model;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Minio.DataModel;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql;
using NPOI.HPSF;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Asn1.Ocsp;
using Quartz.Util;
using RestSharp.Extensions;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using static housingsafety.server.Controllers.HouseMngCountController;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 房屋管理服务
    /// </summary>
    public class HouseMngCjCountController : BaseController<HouseMngController>
    {
        public IConfiguration Configuration { get; }
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildings> _buildingRepository;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IOptions<StaticDataConf> _staticVtOptions;
        private readonly IRepository<ZmDepartment> _departmentRepository;
        private readonly IRepository<ZmPcYhLogDx> _zmPcYhLogDxRepository;
        private readonly IRepository<ZmPcYhLog> _zmPcYhLogRepository;
        private readonly IRepository<ZmPcYh> _zmPcYhRepository;
        private readonly IRepository<ZmPcBase> _zmPcBaseRepository;
        private readonly IRepository<ZmPcBaseLog> _zmPcBaseLogRepository;

        private readonly IDbTransaction _transaction;

        private readonly Microsoft.AspNetCore.Http.Features.FormOptions _defaultFormOptions = new Microsoft.AspNetCore.Http.Features.FormOptions();
        private readonly string[] _permittedExtensions = { ".mp3", ".mp4", ".jpg", ".jpeg", ".png", ".geojson", ".doc", ".docx" };

        private static object _lock = new object();

        private readonly KS3Client _kS3Client;


        public HouseMngCjCountController(IStorageProvider storageProvider
            , ZmDbContext dbContext
            , ILogger<HouseMngController> logger,
            IConfiguration configuration
            , IRepository<ZmSystemLog> systemLogRepository) : base(logger, systemLogRepository)
        {
            _dbContext = dbContext;
            _storageProvider = storageProvider;
            Configuration = configuration;
        }

        public class Param
        {
            public string id { get; set; }

            public string msgid { get; set; }

            public List<DateTime> Time { get; set; }

            public string villagedm { get; set; }
        }



        /// <summary>
        /// 清理电动车统计
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjc/ddzxcwgcd")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> ddzxcwgcd([FromBody] Param Param)
        {
            var usernNumber = this.CurrentUser();
            try
            {

                object par = new { };

                string sql = @" select 
CONCAT('[', STRING_AGG(log_date, ', '), ']') AS x,
CONCAT('[', STRING_AGG(CAST(log_count AS VARCHAR), ', '), ']') AS y from (SELECT TO_CHAR(date_series.log_date, 'YYYY-MM-DD') log_date, COALESCE(log_count, 0) AS log_count
FROM generate_series(CURRENT_DATE - INTERVAL '6 days', CURRENT_DATE, INTERVAL '1 day') date_series(log_date)
LEFT JOIN (
    SELECT date(pcsj) AS log_date, sum(zxqlddcsl) AS log_count
    FROM zm_pc_yhlog a
    LEFT JOIN zm_buildings b ON a.fwbh = b.fwbh
    WHERE date(pcsj) >= CURRENT_DATE - INTERVAL '6 days' and b.villagedm=@village
          AND date(pcsj) < CURRENT_DATE
    GROUP BY date(pcsj)
) AS logs ON date_series.log_date = logs.log_date
ORDER BY date_series.log_date desc)a;
";
                string countsql = @" select 
sum(log_count) log_count
 from (SELECT TO_CHAR(date_series.log_date, 'YYYY-MM-DD') log_date, COALESCE(log_count, 0) AS log_count
FROM generate_series(CURRENT_DATE - INTERVAL '6 days', CURRENT_DATE, INTERVAL '1 day') date_series(log_date)
LEFT JOIN (
    SELECT date(pcsj) AS log_date, sum(zxqlddcsl) AS log_count
    FROM zm_pc_yhlog a
    LEFT JOIN zm_buildings b ON a.fwbh = b.fwbh
    WHERE date(pcsj) >= CURRENT_DATE - INTERVAL '6 days' and b.villagedm=@village
          AND date(pcsj) < CURRENT_DATE
    GROUP BY date(pcsj)
) AS logs ON date_series.log_date = logs.log_date
ORDER BY date_series.log_date desc)a;
";
                par = new { village = Param.villagedm };
                if (Param.Time != null)
                {
                    if (Param.Time.Count == 2)
                    {
                        TimeSpan difference = Param.Time[1].Date - Param.Time[0].Date;
                        int diff = (int)difference.TotalDays;
                        countsql = @$" select 
  sum(log_count) log_count
from  ( SELECT TO_CHAR(date_series.log_date, 'YYYY-MM-DD') log_date, COALESCE(log_count, 0) AS log_count
FROM generate_series(@end - INTERVAL '{diff} days', @end, INTERVAL '1 day') date_series(log_date)
LEFT JOIN (
    SELECT date(pcsj) AS log_date, sum(zxqlddcsl) AS log_count
    FROM zm_pc_yhlog a
    LEFT JOIN zm_buildings b ON a.fwbh = b.fwbh
    WHERE date(pcsj) >= @end - INTERVAL '{diff} days' and b.villagedm=@village
          AND date(pcsj) < @end
    GROUP BY date(pcsj)
) AS logs ON date_series.log_date = logs.log_date
ORDER BY date_series.log_date desc)a ";
                        sql = @$" select 
CONCAT('[', STRING_AGG(log_date, ', '), ']') AS x,
CONCAT('[', STRING_AGG(CAST(log_count AS VARCHAR), ', '), ']') AS y
from  ( SELECT TO_CHAR(date_series.log_date, 'YYYY-MM-DD') log_date, COALESCE(log_count, 0) AS log_count
FROM generate_series(@end - INTERVAL '{diff} days', @end, INTERVAL '1 day') date_series(log_date)
LEFT JOIN (
    SELECT date(pcsj) AS log_date, sum(zxqlddcsl) AS log_count
    FROM zm_pc_yhlog a
    LEFT JOIN zm_buildings b ON a.fwbh = b.fwbh
    WHERE date(pcsj) >= @end - INTERVAL '{diff} days' and b.villagedm=@village
          AND date(pcsj) < @end
    GROUP BY date(pcsj)
) AS logs ON date_series.log_date = logs.log_date
ORDER BY date_series.log_date desc)a ";
                        par = new { village = Param.villagedm, start = Param.Time[0], end = Param.Time[1] };
                    }
                }
                if (string.IsNullOrWhiteSpace(Param.villagedm))
                {
                    sql = sql.Replace("and b.villagedm=@village", "");
                    countsql = countsql.Replace("and b.villagedm=@village", "");
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, par).FirstOrDefault();
                int datacount = _dbContext.Database.GetDbConnection().Query<int>(countsql, par).FirstOrDefault();
                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                if (data != null)
                {
                    var properties = data as IDictionary<string, object>;
                    foreach (var kvp in properties)
                    {
                        var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
                        var list = new List<string>();
                        foreach (var item in stringlist)
                        {
                            list.Add(item);
                        }
                        dictionary.Add(kvp.Key, list);
                    }
                    var r = new RespRet<object>(true, "Ok", new { data = dictionary, datacount = datacount });
                    WriteSystemLog("houses/ddzxcwgcd", r, Param);
                    return r;
                }
                return new RespRet<object>(true, "Ok", new { data = data, datacount = datacount });
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/ddzxcwgcd", res, Param);
                res.Msg = "系统异常";
                return res;
            }
        }


        /// <summary>
        /// 清理电动车统计
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjc/wgsypzyhsyq")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> wgsypzyhsyq([FromBody] Param Param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                object par = new { };
                string sql = @" select 
CONCAT('[', STRING_AGG(log_date, ', '), ']') AS x,
CONCAT('[', STRING_AGG(CAST(log_count AS VARCHAR), ', '), ']') AS y from (SELECT TO_CHAR(date_series.log_date, 'YYYY-MM-DD') log_date, COALESCE(log_count, 0) AS log_count
FROM generate_series(CURRENT_DATE - INTERVAL '6 days', CURRENT_DATE, INTERVAL '1 day') date_series(log_date)
LEFT JOIN (
    SELECT date(pcsj) AS log_date, sum(zxqlyhsyqsl) AS log_count
    FROM zm_pc_yhlog a
    LEFT JOIN zm_buildings b ON a.fwbh = b.fwbh
    WHERE date(pcsj) >= CURRENT_DATE - INTERVAL '6 days' and b.villagedm=@village
          AND date(pcsj) < CURRENT_DATE
    GROUP BY date(pcsj)
) AS logs ON date_series.log_date = logs.log_date
ORDER BY date_series.log_date desc)a;
";
                string countsql = @" select 
sum(log_count) log_count
 from (SELECT TO_CHAR(date_series.log_date, 'YYYY-MM-DD') log_date, COALESCE(log_count, 0) AS log_count
FROM generate_series(CURRENT_DATE - INTERVAL '6 days', CURRENT_DATE, INTERVAL '1 day') date_series(log_date)
LEFT JOIN (
    SELECT date(pcsj) AS log_date, sum(zxqlyhsyqsl) AS log_count
    FROM zm_pc_yhlog a
    LEFT JOIN zm_buildings b ON a.fwbh = b.fwbh
    WHERE date(pcsj) >= CURRENT_DATE - INTERVAL '6 days' and b.villagedm=@village
          AND date(pcsj) < CURRENT_DATE
    GROUP BY date(pcsj)
) AS logs ON date_series.log_date = logs.log_date
ORDER BY date_series.log_date desc)a;
";
                par = new { village = Param.villagedm };
                if (Param.Time != null)
                {
                    if (Param.Time.Count == 2)
                    {
                        TimeSpan difference = Param.Time[1].Date - Param.Time[0].Date;
                        int diff = (int)difference.TotalDays;
                        countsql = @$" select 
  sum(log_count) log_count
from  ( SELECT TO_CHAR(date_series.log_date, 'YYYY-MM-DD') log_date, COALESCE(log_count, 0) AS log_count
FROM generate_series(@end - INTERVAL '{diff} days', @end, INTERVAL '1 day') date_series(log_date)
LEFT JOIN (
    SELECT date(pcsj) AS log_date, sum(zxqlyhsyqsl) AS log_count
    FROM zm_pc_yhlog a
    LEFT JOIN zm_buildings b ON a.fwbh = b.fwbh
    WHERE date(pcsj) >= @end - INTERVAL '{diff} days' and b.villagedm=@village
          AND date(pcsj) < @end
    GROUP BY date(pcsj)
) AS logs ON date_series.log_date = logs.log_date
ORDER BY date_series.log_date desc)a ";
                        sql = @$" select 
CONCAT('[', STRING_AGG(log_date, ', '), ']') AS x,
CONCAT('[', STRING_AGG(CAST(log_count AS VARCHAR), ', '), ']') AS y
from  ( SELECT TO_CHAR(date_series.log_date, 'YYYY-MM-DD') log_date, COALESCE(log_count, 0) AS log_count
FROM generate_series(@end - INTERVAL '{diff} days', @end, INTERVAL '1 day') date_series(log_date)
LEFT JOIN (
    SELECT date(pcsj) AS log_date, sum(zxqlyhsyqsl) AS log_count
    FROM zm_pc_yhlog a
    LEFT JOIN zm_buildings b ON a.fwbh = b.fwbh
    WHERE date(pcsj) >= @end - INTERVAL '{diff} days' and b.villagedm=@village
          AND date(pcsj) < @end
    GROUP BY date(pcsj)
) AS logs ON date_series.log_date = logs.log_date
ORDER BY date_series.log_date desc)a ";
                        par = new { village = Param.villagedm, start = Param.Time[0], end = Param.Time[1] };
                    }
                }
                if (string.IsNullOrWhiteSpace(Param.villagedm))
                {
                    sql = sql.Replace("and b.villagedm=@village", "");
                    countsql = countsql.Replace("and b.villagedm=@village", "");
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, par).FirstOrDefault();
                int datacount = _dbContext.Database.GetDbConnection().Query<int>(countsql, par).FirstOrDefault();
                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                if (data != null)
                {
                    var properties = data as IDictionary<string, object>;
                    foreach (var kvp in properties)
                    {
                        var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
                        var list = new List<string>();
                        foreach (var item in stringlist)
                        {
                            list.Add(item);
                        }
                        dictionary.Add(kvp.Key, list);
                    }
                    var r = new RespRet<object>(true, "Ok", new { data = dictionary, datacount = datacount });
                    WriteSystemLog("houses/wgsypzyhsyq", r, Param);
                    return r;
                }
                return new RespRet<object>(true, "Ok", new { data = data, datacount = datacount });
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/wgsypzyhsyq", res, Param);
                res.Msg = "系统异常";
                return res;
            }
        }




        /// <summary>
        /// 自定义sql
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjc/excute")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> excute([FromBody] Param Param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (string.IsNullOrWhiteSpace(Param.id))
                {
                    return new RespRet<object>(false, "id不能为空");
                }
                var sql = _dbContext.ZmSql.FirstOrDefault(x => x.Id == Param.id);
                var sqlParams = new List<NpgsqlParameter>();
                if (sql == null)
                {
                    return new RespRet<object>(false, "未找到可执行语句，请传递正确id");
                }
                object par = new { };
                if (string.IsNullOrWhiteSpace(Param.villagedm))
                {
                    sql.Value = sql.Value.Replace("and village=@village", " ");
                    sql.Value = sql.Value.Replace("and b.villagedm=@village", " ");
                }
                else
                {
                    par = new { village = Param.villagedm };
                }
                if (Param.Time != null)
                {
                    if (Param.Time.Count == 2)
                    {
                        par = new { village = Param.villagedm, start = Param.Time[0], end = Param.Time[1] };
                    }
                    else
                    {
                        sql.Value = sql.Value.Replace("and date(pcsj) between @start and @end", " ");
                    }
                }
                else
                {
                    sql.Value = sql.Value.Replace("and date(pcsj) between @start and @end", " ");
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql.Value, par).First();
                return new RespRet<object>(true, "Ok", data);
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/excute", res, Param);
                res.Msg = "系统异常";
                return res;
            }
        }

        static List<string> ConvertObjectFieldsToList(object obj)
        {
            List<string> stringList = new List<string>();
            // Use reflection to get type information
            Type type = obj.GetType();
            PropertyInfo[] properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
            foreach (PropertyInfo property in properties)
            {
                // Get the value of each property
                object value = property.Name; // Pass null for no parameters
                // Convert the value to string (or "null" if it's null)
                string stringValue = (value != null) ? value.ToString() : "null";
                // Add the string value to the list
                stringList.Add(stringValue);
            }

            return stringList;
        }



        static Dictionary<string, object> ConvertDynamicObjectToDictionary(dynamic obj)
        {
            Dictionary<string, object> dictionary = new Dictionary<string, object>();
            if (obj != null)
            {
                var properties = obj as IDictionary<string, object>;
                foreach (var kvp in properties)
                {
                    dictionary.Add(kvp.Key, kvp.Value);
                }
            }
            return dictionary;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjc/excutetb")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> Excutetb(Param param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (string.IsNullOrWhiteSpace(param.id))
                {
                    return new RespRet<object>(false, "id不能为空");
                }
                var sql = _dbContext.ZmSql.FirstOrDefault(x => x.Id == param.id);
                if (sql == null)
                {
                    return new RespRet<object>(false, "未找到可执行语句，请传递正确id");
                }
                object par = new { };
                if (string.IsNullOrWhiteSpace(param.villagedm))
                {
                    sql.Value = sql.Value.Replace("and village=@village", " ");
                }
                else
                {
                    par = new { village = param.villagedm };
                }
                if (param.Time != null)
                {
                    if (param.Time.Count == 2)
                    {
                        par = new { village = param.villagedm, start = param.Time[0], end = param.Time[1] };
                    }
                    else
                    {
                        sql.Value = sql.Value.Replace("and date(pcsj) between @start and @end", " ");
                    }
                }
                else
                {
                    sql.Value = sql.Value.Replace("and date(pcsj) between @start and @end", " ");
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql.Value, par).FirstOrDefault();

                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                if (data != null)
                {
                    var properties = data as IDictionary<string, object>;
                    foreach (var kvp in properties)
                    {
                        var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
                        var list = new List<string>();
                        foreach (var item in stringlist)
                        {
                            list.Add(item);
                        }
                        dictionary.Add(kvp.Key, list);
                    }
                    var r = new RespRet<object>(true, "Ok", dictionary);
                    WriteSystemLog("houses/excute", r, param);
                    return r;
                }
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("houses/excute", res, param);
                return res;
            }

            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("houses/excute", res, param);
                res.Msg = "系统异常";
                return res;
            }
        }




        [HttpPost("housesjc/excuteall")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> ExcuteAll([FromBody] Param param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (string.IsNullOrWhiteSpace(param.id))
                {
                    return new RespRet<object>(false, "id不能为空");
                }
                if (string.IsNullOrWhiteSpace(param.msgid))
                {
                    return new RespRet<object>(false, "msgid不能为空");
                }
                var sql = _dbContext.ZmSql.FirstOrDefault(x => x.Id == param.id);
                if (sql == null)
                {
                    return new RespRet<object>(false, "未找到可执行语句，请传递正确id");
                }

                var bzsql = _dbContext.ZmSql.FirstOrDefault(x => x.Id == param.msgid);
                if (bzsql == null)
                {
                    return new RespRet<object>(false, "未找到可执行语句，请传递正确msgid");
                }
                object par = new { };
                if (string.IsNullOrWhiteSpace(param.villagedm))
                {
                    sql.Value = sql.Value.Replace("and village=@village", " ");
                    bzsql.Value = bzsql.Value.Replace("and village=@village", " ");
                    sql.Value = sql.Value.Replace("and villagedm=@village", " ");
                    bzsql.Value = bzsql.Value.Replace("and villagedm=@village", " ");
                }
                else
                {
                    par = new { village = param.villagedm };
                }
                if (param.Time != null)
                {
                    if (param.Time.Count == 2)
                    {
                        par = new { village = param.villagedm, start = param.Time[0], end = param.Time[1] };
                    }
                    else
                    {
                        sql.Value = sql.Value.Replace("and date(pcsj) between @start and @end", " ");
                        bzsql.Value = bzsql.Value.Replace("and date(pcsj) between @start and @end", " ");
                    }
                }
                else
                {
                    sql.Value = sql.Value.Replace("and date(pcsj) between @start and @end", " ");
                    bzsql.Value = bzsql.Value.Replace("and date(pcsj) between @start and @end", " ");
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql.Value, par).FirstOrDefault();
                var msgdata = _dbContext.Database.GetDbConnection().Query<object>(bzsql.Value, par).FirstOrDefault();
                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                if (data != null)
                {
                    var properties = data as IDictionary<string, object>;
                    foreach (var kvp in properties)
                    {
                        var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
                        var list = new List<string>();
                        foreach (var item in stringlist)
                        {
                            list.Add(item);
                        }
                        dictionary.Add(kvp.Key, list);
                    }
                    var r = new RespRet<object>(true, "Ok", new { tb = dictionary, msg = msgdata });
                    WriteSystemLog("houses/excute", r, param);
                    return r;
                }
                var res = new RespRet<object>(true, "Ok", new { tb = dictionary, msg = msgdata });
                WriteSystemLog("houses/excute", res, param);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("houses/excute", res, param);
                res.Msg = "系统异常";
                return res;
            }
        }

        private List<string> JsonStr2List(string jsonStr)
        {
            try
            {
                return JsonConvert.DeserializeObject<List<string>>(jsonStr);
            }
            catch (System.Exception)
            {
            }

            return null;
        }


        private object JsonRegion(string jsonStr)
        {
            try
            {
                return JsonConvert.DeserializeObject<object>(jsonStr);
            }
            catch (System.Exception)
            {
            }

            return null;
        }

        /// <summary>
        /// 统计分析，今日自查情况
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("housesjc/tjfx-jrzcqk")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> TjfxJrzcqk([FromBody] Param param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber && c.IsDelete != 1);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                if (RegionCode != param.villagedm)
                {
                    var r1 = new RespRet<object>(false, "请传递正确的村代码");
                    WriteSystemLog("housesjc/tjfx-jrzcqk", r1, param);
                    return r1;
                }
                string sql = " select *,(fwzs::int-yzc::int) wzc,round(yzc::numeric*100/fwzs::numeric,2)  zcl  from (select  (select fwds fwzs from zm_building_ledger where villagedm=@villagedm) fwzs,(select count(1) yzc from zm_pc_yh where villagedm=@villagedm) yzc,(select count(case when  date(create_on)=date(now()) then 1 end) jrzc from zm_pc_yh where villagedm=@villagedm )jrzc)a ";


                object par = new { villagedm = param.villagedm };

                if (param.Time != null)
                {
                    if (param.Time.Count == 2)
                    {
                        sql = " select *,(fwzs::int-yzc::int) wzc,round(yzc::numeric*100/fwzs::numeric,2)  zcl  from (select  (select fwds fwzs from zm_building_ledger where villagedm=@villagedm) fwzs,(select count(1) yzc from zm_pc_yh where villagedm=@villagedm )yzc,(select count(case when  date(create_on)=date(now()) then 1 end) jrzc from zm_pc_yh where villagedm=@villagedm)jrzc)a   ";
                        par = new { villagedm = param.villagedm, start = param.Time[0], end = param.Time[1] };
                    }
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, par).FirstOrDefault();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housesjc/tjfx-jrzcqk", res, param);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/tjfx-jrzcqk", res, param);
                res.Msg = "系统异常";
                return res;
            }
        }





        /// <summary>
        /// 市级统计分析，今日自查情况
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("housesjc/zctj")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> Zctj([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            if (string.IsNullOrWhiteSpace(usernNumber))
            {
                return new RespRet<object>(false, "用户没有登录");
            }
            try
            {
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                string sql = "";
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;


                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }

                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;


                if (RegionCode.Length == 3 || RegionCode.Length == 6)
                {
                    sql = @" select wzccount,zcount,zccount,yhcount,zgcount,case when value >100 then 100.00||'%' WHEN value IS NULL THEN 0||'%' else value||'%' end zcl,case when value >100 then 100.00 WHEN value IS NULL THEN 0 else value end value   from   (select sum(wjccount) wzccount,sum(zcount) zcount,sum(jccount) zccount,sum(yhcount) yhcount,sum(zgcount) zgcount,round(sum(jccount)*1.00*100/sum(zcount),2)||'%' zcl, round(sum(jccount)*1.00*100/sum(zcount),2) value from jcqkgqtext(@startime,@endtime::TIMESTAMP,@tablename))a
 ";
                }

                if (RegionCode.Length == 9)
                {
                    sql = @" select wzccount,zcount,zccount,yhcount,zgcount,case when value >100 then 100.00||'%' WHEN value IS NULL THEN 0||'%' else value||'%' end zcl,case when value >100 then 100.00 WHEN value IS NULL THEN 0 else value end value    from   (select sum(wjccount) wzccount,sum(zcount) zcount,sum(jccount) zccount,sum(yhcount) yhcount,sum(zgcount) zgcount,round(sum(jccount)*1.00*100/sum(zcount),2)||'%' zcl, round(sum(jccount)*1.00*100/sum(zcount),2) value from jcqkxztext(@startime,@endtime::TIMESTAMP,@code,@tablename) where towndm like @code)a
   ";
                }
                if (RegionCode.Length == 12)
                {
                    sql = @" select wzccount,zcount,zccount,yhcount,zgcount,case when value >100 then 100.00||'%' WHEN value IS NULL THEN 0||'%' else value||'%' end zcl,case when value >100 then 100.00 WHEN value IS NULL THEN 0 else value end value   from   (select sum(wjccount) wzccount,sum(zcount) zcount,sum(jccount) zccount,sum(yhcount) yhcount,sum(zgcount) zgcount,round(sum(jccount)*1.00*100/sum(zcount),2)||'%' zcl, round(sum(jccount)*1.00*100/sum(zcount),2) value from jcqkcjtext(@startime,@endtime::TIMESTAMP,@code,@tablename) where villagedm like @code)a
   ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new {@tablename=table_name, @code = "%" + RegionCode + "%", @startime = param.date[0], @endtime = formattedDate }).FirstOrDefault();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housesjc/zctj", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/zctj", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }



        /// <summary>
        /// 市级统计分析，今日自查情况
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("housesjc/zcyhtj")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> zcyhtj([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            if (string.IsNullOrWhiteSpace(usernNumber))
            {
                return new RespRet<object>(false, "用户没有登录");
            }
            try
            {
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                string sql = "";
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;


                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }

                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;

                if (RegionCode.Length == 3 || RegionCode.Length == 6)
                {
                    sql = @" select sum(zcount) zcount,
sum(jccount) jccount,
sum(wjccount) wjccount,
case when sum(zcount)=0 then 0  else (case when round(sum(jccount)*100.00/sum(zcount),2)>100 then 100 else round(sum(jccount)*100.00/sum(zcount),2) end)  end  zcl,
sum(yhcount) yhcount,
case when sum(jccount)=0 then 0 else (case when round(sum(yhcount_d)*100.00/sum(jccount),2)>100 then 100 else round(sum(yhcount_d)*100.00/sum(jccount),2) end)  end yhfxl,
sum(zgcount) zgcount,
case when sum(yhcount)=0  then 0 else (case when  round(sum(zgcount)*100.00/sum(yhcount),2)>100 then 100 else round(sum(zgcount)*100.00/sum(yhcount),2) end) end yhzgl
from jcqkgqtext(@startime,@endtime::TIMESTAMP,@tablename) t1; 
  ";
                }
               
                if (RegionCode.Length == 9)
                {
                    sql = @" select sum(zcount) zcount,
sum(jccount) jccount,
sum(wjccount) wjccount,
case when sum(zcount)=0 then 0  else (case when round(sum(jccount)*100.00/sum(zcount),2)>100 then 100 else round(sum(jccount)*100.00/sum(zcount),2) end)  end  zcl,
sum(yhcount) yhcount,
case when sum(jccount)=0 then 0 else (case when round(sum(yhcount_d)*100.00/sum(jccount),2)>100 then 100 else round(sum(yhcount_d)*100.00/sum(jccount),2) end)  end yhfxl,
sum(zgcount) zgcount,
case when sum(yhcount)=0  then 0 else (case when  round(sum(zgcount)*100.00/sum(yhcount),2)>100 then 100 else round(sum(zgcount)*100.00/sum(yhcount),2) end) end yhzgl
from jcqkxztext(@startime,@endtime::TIMESTAMP,@code,@tablename) t1 where towndm like @code;  ";
                }
                if (RegionCode.Length == 12)
                {
                    sql = @" select sum(zcount) zcount,
sum(jccount) jccount,
sum(wjccount) wjccount,
case when sum(zcount)=0 then 0  else (case when round(sum(jccount)*100.00/sum(zcount),2)>100 then 100 else round(sum(jccount)*100.00/sum(zcount),2) end)  end  zcl,
sum(yhcount) yhcount,
case when sum(jccount)=0 then 0 else (case when round(sum(yhcount_d)*100.00/sum(jccount),2)>100 then 100 else round(sum(yhcount_d)*100.00/sum(jccount),2) end)  end yhfxl,
sum(zgcount) zgcount,
case when sum(yhcount)=0  then 0 else (case when  round(sum(zgcount)*100.00/sum(yhcount),2)>100 then 100 else round(sum(zgcount)*100.00/sum(yhcount),2) end) end yhzgl
from jcqkcjtext(@startime,@endtime::TIMESTAMP,@code,@tablename) t1 where villagedm like @code;    ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @tablename=table_name, @code = "%" + RegionCode + "%", @startime = param.date[0], @endtime = formattedDate }).FirstOrDefault();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housesjc/zcyhtj", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/zcyhtj", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }



       




        /// <summary>
        /// 自查行政区域发现隐患率统计
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("housesjc/fxyhltj")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> fxyhltj([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            if (string.IsNullOrWhiteSpace(usernNumber))
            {
                return new RespRet<object>(false, "用户没有登录");
            }
            try
            {
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                string sql = "";
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
                bool isxz = true;
                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;

                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }

                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;

                if (param.code.Length == 3 || param.code.Length == 6)
                {
                    sql = @" 
select xzqdm code,xzq name,case when yhcount is null then 0 else yhcount end yhcount,case when zccount is null then 0 else zccount end zccount,
case when round(yhcount*1.00*100/zccount,2) is null then 0.00 when round(yhcount*1.00*100/zccount,2)>100 then 100.00 else round(yhcount*1.00*100/zccount,2) end  yhfxl    from  (select
xzqdm,max(xzq) xzq,
case when  sum(jccount) =0 then null else sum(jccount) end zccount,
case when  sum(yhcount) =0 then null else sum(yhcount) end yhcount  from jcqkgqtext_fz(@startime,@endtime::TIMESTAMP,@tablename) GROUP BY xzqdm ) a ORDER BY yhfxl desc,xzqdm  ";
                }
                if (isxz)
                {
                    if (param.code.Length == 6)
                    {
                        sql = @" select towndm code,town name,case when yhcount is null then 0 else yhcount end yhcount,case when zccount is null then 0 else zccount end zccount,
                    case when round(yhcount*1.00*100/zccount,2) is null then 0.00 when round(yhcount*1.00*100/zccount,2)>100 then 100.00 else round(yhcount*1.00*100/zccount,2) end  yhfxl    from  (select
                    towndm,max(town) town,
                    case when  sum(jccount) =0 then null else sum(jccount) end zccount,
                    case when  sum(yhcount) =0 then null else sum(yhcount) end yhcount  from jcqkxztext_fz(@startime,@endtime::TIMESTAMP,@code,@tablename) where towndm like @code GROUP BY towndm ) a ORDER BY yhfxl desc,towndm  ";
                    }
                }
                if (param.code.Length == 9)
                {
                    sql = @" 
select villagedm code,village name,case when yhcount is null then 0 else yhcount end yhcount,case when zccount is null then 0 else zccount end zccount,
case when round(yhcount*1.00*100/zccount,2) is null then 0.00 when round(yhcount*1.00*100/zccount,2)>100 then 100.00 else round(yhcount*1.00*100/zccount,2) end  yhfxl    from  (select
villagedm,max(village) village,
case when  sum(jccount) =0 then null else sum(jccount) end zccount,
case when  sum(yhcount) =0 then null else sum(yhcount) end yhcount  from jcqkcjtext_fz(@startime,@endtime::TIMESTAMP,@code,@tablename) where villagedm like @code GROUP BY villagedm ) a ORDER BY yhfxl desc,villagedm   ";
                }
                if (param.code.Length == 12)
                {
                    sql = @" select villagedm code,village name,case when yhcount is null then 0 else yhcount end yhcount,case when zccount is null then 0 else zccount end zccount,
case when round(yhcount*1.00*100/zccount,2) is null then 0.00 when round(yhcount*1.00*100/zccount,2)>100 then 100.00 else round(yhcount*1.00*100/zccount,2) end  yhfxl    from  (select
villagedm,max(village) village,
case when  sum(jccount) =0 then null else sum(jccount) end zccount,
case when  sum(yhcount) =0 then null else sum(yhcount) end yhcount  from jcqkcjtext_fz(@startime,@endtime::TIMESTAMP,@code,@tablename) where villagedm like @code GROUP BY villagedm ) a ORDER BY yhfxl desc,villagedm   ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @tablename=table_name,@code = "%" + param.code + "%", @startime = param.date[0], @endtime = formattedDate }).ToList();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housesjc/fxyhltj", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/fxyhltj", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }


        /// <summary>
        /// 自查行政区域整改隐患率统计
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost("housesjc/zgyhltj")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> zgyhltj([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            if (string.IsNullOrWhiteSpace(usernNumber))
            {
                return new RespRet<object>(false, "用户没有登录");
            }
            try
            {
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                string sql = "";
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
                bool isxz = true;
                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;


                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }
                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;

                if (param.code.Length == 3 || param.code.Length == 6)
                {
                    sql = @" 
select xzqdm code,xzq name,case when yhcount is null then 0 else yhcount end yhcount,case when zgcount is null then 0 else zgcount end zgcount,
case when round(zgcount*1.00*100/yhcount,2) is null then 0.00 when round(zgcount*1.00*100/yhcount,2)>100 then 100.00  else  round(zgcount*1.00*100/yhcount,2) end  yhzgl    from  (select
xzqdm,max(xzq) xzq,
case when  sum(zgcount) is null then 0.00 else sum(zgcount) end zgcount,
case when  sum(yhcount) =0 then null else sum(yhcount) end yhcount  from jcqkgqtext(@startime,@endtime::TIMESTAMP,@tablename) GROUP BY xzqdm ) a ORDER BY yhzgl desc,xzqdm  ";
                }
                if (isxz)
                {
                    if (param.code.Length == 6)
                    {
                        sql = @" select towndm code,town name,case when yhcount is null then 0 else yhcount end yhcount,case when zgcount is null then 0 else zgcount end zgcount,
                    case when round(zgcount*1.00*100/yhcount,2) is null then 0.00 when round(zgcount*1.00*100/yhcount,2)>100 then 100.00 else round(zgcount*1.00*100/yhcount,2) end  yhzgl    from  (select
                    towndm,max(town) town,
                    case when  sum(zgcount) is null then 0.00 else sum(zgcount) end zgcount,
                    case when  sum(yhcount) =0 then null else sum(yhcount) end yhcount  from jcqkxztext(@startime,@endtime::TIMESTAMP,@code,@tablename) where towndm like @code GROUP BY towndm ) a ORDER BY yhzgl desc,towndm  ";
                    }
                }
                if (param.code.Length == 9)
                {
                    sql = @" 
select villagedm code,village name,case when yhcount is null then 0 else yhcount end yhcount,case when zgcount is null then 0 else zgcount end zgcount,
case when round(zgcount*1.00*100/yhcount,2) is null then 0.00 when round(zgcount*1.00*100/yhcount,2)>100 then 100.00 else round(zgcount*1.00*100/yhcount,2) end  yhzgl    from  (select
villagedm,max(village) village,
case when  sum(zgcount) is null then 0.00 else sum(zgcount) end zgcount,
case when  sum(yhcount) =0 then null else sum(yhcount) end yhcount  from jcqkcjtext(@startime,@endtime::TIMESTAMP,@code,@tablename) where villagedm like @code GROUP BY villagedm ) a ORDER BY yhzgl desc,villagedm   ";
                }
                if (param.code.Length == 12)
                {
                    sql = @" select villagedm code,village name,case when yhcount is null then 0 else yhcount end yhcount,case when zgcount is null then 0 else zgcount end zgcount,
case when round(zgcount*1.00*100/yhcount,2) is null then 0.00 when round(zgcount*1.00*100/yhcount,2)>100 then 100.00 else round(zgcount*1.00*100/yhcount,2) end  yhzgl    from  (select
villagedm,max(village) village,
case when  sum(zgcount) is null then 0.00 else sum(zgcount) end zgcount,
case when  sum(yhcount) =0 then null else sum(yhcount) end yhcount  from jcqkcjtext(@startime,@endtime::TIMESTAMP,@code,@tablename) where villagedm like @code GROUP BY villagedm ) a ORDER BY yhzgl desc,villagedm   ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new {@tablename=table_name, @code = "%" + param.code + "%", @startime = param.date[0], @endtime =formattedDate }).ToList();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housesjc/zgyhltj", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/zgyhltj", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }



        /// <summary>
        /// 市级自查饼图
        /// </summary>
        /// <returns></returns>

        [HttpPost("housesjc/zcpie")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> ExcutePie([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                string sql = "  ";
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;


                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }
                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;


                if (RegionCode.Length == 3 || RegionCode.Length == 6)
                {
                    sql = @"  select '已检查' name,case when sum(zcount)=0 then 0  else (case when round(sum(jccount)*100.00/sum(zcount),2)>100 then 100 else round(sum(jccount)*100.00/sum(zcount),2) end)  end value
from jcqkgqtext(@startime,@endtime::TIMESTAMP,@tablename) t1   ";
                }
                //if (RegionCode.Length == 6)
                //{
                //    sql = @" select zcount,zccount,yhcount,zgcount,case when value >100 then 100.00||'%' WHEN value IS NULL THEN 0||'%' else value||'%' end zcl,case when value >100 then 100.00 WHEN value IS NULL THEN 0 else value end value   from   (select sum(zcount) zcount,sum(zccount) zccount,sum(yhcount) yhcount,sum(zgcount) zgcount,round(sum(zccount)*1.00*100/sum(zcount),2)||'%' zcl, round(sum(zccount)*1.00*100/sum(zcount),2) value from zcqkgqtext(@startime,@endtime) where xzqdm like @code)a  ";
                //}
                if (RegionCode.Length == 9)
                {
                    sql = @" select '已检查' name, case when sum(zcount)=0 then 0  else (case when round(sum(jccount)*100.00/sum(zcount),2)>100 then 100 else round(sum(jccount)*100.00/sum(zcount),2) end) end value
from jcqkxztext(@startime,@endtime::TIMESTAMP,@code,@tablename) t1 where towndm like @code ";
                }
                if (RegionCode.Length == 12)
                {
                    sql = @" select '已检查' name, case when sum(zcount)=0 then 0  else (case when round(sum(jccount)*100.00/sum(zcount),2)>100 then 100 else round(sum(jccount)*100.00/sum(zcount),2) end) end value
from jcqkcjtext(@startime,@endtime::TIMESTAMP,@code,@tablename) t1 where villagedm like @code ";
                }
                List<PieDto> res_data = new List<PieDto>();

                var data = _dbContext.Database.GetDbConnection().Query<PieDto>(sql, new { @tablename = table_name, @code = "%" + RegionCode + "%", @startime = param.date[0], @endtime = formattedDate }).FirstOrDefault();
                res_data.Add(data);
                res_data.Add(new PieDto { name = "未检查", value = 100 - data.value });

                var res = new RespRet<object>(true, "Ok", res_data);
                WriteSystemLog("housesjc/zcpie", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/zcpie", res, param);
                res.Msg = "系统异常";
                return res;
            }
        }






        [HttpPost("housesjc/excutepie")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> ExcutePie([FromBody] Param param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (string.IsNullOrWhiteSpace(param.id))
                {
                    return new RespRet<object>(false, "id不能为空");
                }
                if (string.IsNullOrWhiteSpace(param.msgid))
                {
                    return new RespRet<object>(false, "msgid不能为空");
                }
                var sql = _dbContext.ZmSql.FirstOrDefault(x => x.Id == param.id);
                if (sql == null)
                {
                    return new RespRet<object>(false, "未找到可执行语句，请传递正确id");
                }

                var bzsql = _dbContext.ZmSql.FirstOrDefault(x => x.Id == param.msgid);
                if (bzsql == null)
                {
                    return new RespRet<object>(false, "未找到可执行语句，请传递正确msgid");
                }
                object par = new { };
                if (string.IsNullOrWhiteSpace(param.villagedm))
                {
                    sql.Value = sql.Value.Replace("and village=@village", " ");
                    bzsql.Value = bzsql.Value.Replace("and village=@village", " ");
                    sql.Value = sql.Value.Replace("and villagedm=@village", " ");
                    bzsql.Value = bzsql.Value.Replace("and villagedm=@village", " ");
                }
                else
                {
                    par = new { village = param.villagedm };
                }
                if (param.Time != null)
                {
                    if (param.Time.Count == 2)
                    {
                        par = new { village = param.villagedm, start = param.Time[0], end = param.Time[1] };
                    }
                    else
                    {
                        sql.Value = sql.Value.Replace("and date(pcsj) between @start and @end", " ");
                        bzsql.Value = bzsql.Value.Replace("and date(pcsj) between @start and @end", " ");
                    }
                }
                else
                {
                    sql.Value = sql.Value.Replace("and date(pcsj) between @start and @end", " ");
                    bzsql.Value = bzsql.Value.Replace("and date(pcsj) between @start and @end", " ");
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql.Value, par).ToList();
                var msgdata = _dbContext.Database.GetDbConnection().Query<object>(bzsql.Value, par).FirstOrDefault();
                var res = new RespRet<object>(true, "Ok", new { tb = data, msg = msgdata });
                WriteSystemLog("houses/excutepie", res, param);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("houses/excutepie", res, param);
                res.Msg = "系统异常";
                return res;
            }
        }




        /// <summary>
        /// 已自查为自查饼状图二级页面，按率倒序排
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjc/zclbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> Zclbar([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                string sql = " select  *,jcl zcl,jccount zccount from( select *,replace(jcl, '%','') value from jcqkgqtext(@startime,@endtime) ORDER BY replace(jcl, '%','')::numeric   desc,xzqdm)a ";


                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
                bool isxz = true;
                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;


                if (param.date[1] > DateTime.Now)
                {
                    param.date[1] = DateTime.Today;
                }
                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + param.date[1].Year;


                if (param.code.Length == 3 || param.code.Length == 6)
                {
                    sql = @" select  *,jcl zcl,jccount zccount from( select *,replace(jcl, '%','') value from jcqkgqtext(@startime,@endtime::TIMESTAMP,@tablename) ORDER BY replace(jcl, '%','')::numeric   desc,xzqdm)a  ";
                }
                if (isxz)
                {
                    if (param.code.Length == 6)
                    {
                        sql = @" select  *,jcl zcl,jccount zccount from( select *,replace(jcl, '%','') value from jcqkxztext(@startime,@endtime::TIMESTAMP,@code,@tablename) where towndm like @code ORDER BY replace(jcl, '%','')::numeric   desc,towndm)a   ";
                    }
                }

                if (param.code.Length == 9 || (param.code.Length == 12))
                {
                    sql = @" select  *,jcl zcl,jccount zccount from( select *,replace(jcl, '%','') value from jcqkcjtext(@startime,@endtime::TIMESTAMP,@code,@tablename) where villagedm like @code ORDER BY replace(jcl, '%','')::numeric   desc,villagedm)a    ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new {@tablename=table_name, @code = "%" + param.code + "%", @startime = param.date[0], @endtime = formattedDate }).ToList();
               
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housesjc/zclbar", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/zclbar", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }




        /// <summary>
        /// 市级 获取自查率 各区县的总数量  自查数量  隐患数量  整改数量
        /// </summary>
        /// <param name="pageRequest"></param>         
        /// <returns></returns>
        [HttpGet("housesjc/checkrategq")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<object> HouseMngCheckQ()
        {
            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
          
            string sql = @" select *,jccount zccount,jcl zcl,wjccount wzccount,replace(jcl, '%', '')::numeric value,xzq name,xzqdm code  from jcqkgqview  ";
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<object>(true, "Ok", list);
        }


        /// <summary>
        /// 市级 获取自查率 各乡镇的总数量  自查数量 自查整改数量  自查隐患数量等
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/checkrategxz")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> HouseMngCheckxz([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;

           
            string sql = @" select *,jccount zccount,jcl zcl,wjccount wzccount,replace(jcl, '%', '')::numeric value,town name,towndm code  from jcqkxzview where towndm like @code	";

            var list = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).ToList();
            int count = list.Count;
            return new RespRet<object>(true, "Ok", list);
        }


        /// <summary>
        /// 市级 获取自查率 各村的总数量  自查数量 自查整改数量  自查隐患数量等
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/checkrategcj")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> HouseMngCheckcj([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;

           
            string sql = @" select *,jccount zccount,jcl zcl,wjccount wzccount,replace(jcl, '%', '')::numeric value,village name,villagedm code  from jcqkcjview where villagedm like @code	";

            var list = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).ToList();
            return new RespRet<object>(true, "Ok", list);
        }


        /// <summary>
        /// 市级-人员疏散通道
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/rysstd")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> Rysstd([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
           
            string tbsql = @$" ";
            string sql = @" ";
            if (code.Length == 3)
            {
                tbsql = @$" 
select case when value is null then 0.00 else value end,name From ( select round(sum(ssltdbfjsl)*1.00*100/sum(zcount),2) value, '已达标' name  from  jcjbxxgqview union all select round((sum(zcount)-sum(ssltdbfjsl))*1.00*100/sum(zcount),2), '未达标' name from  jcjbxxgqview )a ";
                sql = @" select ydb,case when  round(ydb*1.00*100/zcount,2) is null then 0.00 else round(ydb*1.00*100/zcount,2) end   dbl from  (select case when  sum(ssltdbfjsl) is null then 0.00 else  sum(ssltdbfjsl) end ydb ,sum(zcount) zcount  from  jcjbxxgqview) a 	";
            }
            if (code.Length == 6)
            {
                tbsql = @$" select case when value is null then 0.00 else value end,name From ( select round(sum(ssltdbfjsl)*1.00*100/sum(zcount),2) value, '已达标' name  from  jcjbxxgqview where xzqdm like @code  union all select round((sum(zcount)-sum(ssltdbfjsl))*1.00*100/sum(zcount),2), '未达标' name from  jcjbxxgqview where xzqdm like @code )a	 ";
                sql = @" select ydb,case when  round(ydb*1.00*100/zcount,2) is null then 0.00 else round(ydb*1.00*100/zcount,2) end   dbl from  (select case when  sum(ssltdbfjsl) is null then 0.00 else  sum(ssltdbfjsl) end ydb ,sum(zcount) zcount  from  jcjbxxgqview where xzqdm like @code) a 	 ";
            }
            if (code.Length == 9)
            {
                tbsql = @$" select case when value is null then 0.00 else value end,name From ( select round(sum(ssltdbfjsl)*1.00*100/sum(zcount),2) value, '已达标' name  from  jcjbxxxzview where towndm like @code union all select round((sum(zcount)-sum(ssltdbfjsl))*1.00*100/sum(zcount),2), '未达标' name from  jcjbxxxzview where towndm like @code )a ";
                sql = @" select ydb,case when  round(ydb*1.00*100/zcount,2) is null then 0.00 else round(ydb*1.00*100/zcount,2) end   dbl from  (select case when  sum(ssltdbfjsl) is null then 0.00 else  sum(ssltdbfjsl) end ydb ,sum(zcount) zcount  from  jcjbxxxzview where towndm like @code) a  ";
            }
            if (code.Length == 12)
            {
                tbsql = @$" select case when value is null then 0.00 else value end,name From ( select round(sum(ssltdbfjsl)*1.00*100/sum(zcount),2) value, '已达标' name  from  jcjbxxcjview where villagedm like @code union all select round((sum(zcount)-sum(ssltdbfjsl))*1.00*100/sum(zcount),2), '未达标' name from  jcjbxxcjview where villagedm like @code )a ";
                sql = @" select ydb,case when  round(ydb*1.00*100/zcount,2) is null then 0.00 else round(ydb*1.00*100/zcount,2) end   dbl from  (select case when  sum(ssltdbfjsl) is null then 0.00 else  sum(ssltdbfjsl) end ydb ,sum(zcount) zcount  from  jcjbxxcjview where villagedm like @code) a 	";
            }

            var tb = _dbContext.Database.GetDbConnection().Query<object>(tbsql, new { @code = "%" + code + "%" }).ToList();
            var msg = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).FirstOrDefault();
            return new RespRet<object>(true, "Ok", new { tb = tb, msg = msg });
        }



        /// <summary>
        /// 已达标未达标饼状图二级页面，按达标率倒序排
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjc/dblbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> Dblbar([FromQuery] string code)
        {
            try
            {
                var usernNumber = this.CurrentUser();
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
                if (string.IsNullOrWhiteSpace(code))
                {
                    code = RegionCode;
                }
                code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
              
                string sql = @" ";
                if (code.Length == 3)
                {
                    sql = @" select    name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select xzq name,xzqdm code,case when  round(ydb*1.00*100/zcount,2) is null then 0.00 else round(ydb*1.00*100/zcount,2) end   dbl from  (select max(xzq) xzq,xzqdm,case when  sum(ssltdbfjsl) is null then 0.00 else  sum(ssltdbfjsl) end ydb ,sum(zcount) zcount  from  jcjbxxgqview GROUP BY xzqdm ) a ORDER BY dbl desc )a  ";
                }
                if (code.Length == 6)
                {
                    sql = @" select  name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl from  (select town name,towndm code,case when  round(ydb*1.00*100/zcount,2) is null then 0.00 else round(ydb*1.00*100/zcount,2) end   dbl from  (select max(town) town,towndm,case when  sum(ssltdbfjsl) is null then 0.00 else  sum(ssltdbfjsl) end ydb ,sum(zcount) zcount  from  jcjbxxxzview where towndm like @code GROUP BY towndm ) a ORDER BY dbl desc )a     ";
                }
                if (code.Length == 9)
                {
                    sql = @" select  name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select village name,villagedm code,case when  round(ydb*1.00*100/zcount,2) is null then 0.00 else round(ydb*1.00*100/zcount,2) end   dbl from  (select max(village) village,villagedm,case when  sum(ssltdbfjsl) is null then 0.00 else  sum(ssltdbfjsl) end ydb ,sum(zcount) zcount  from  jcjbxxcjview where villagedm like @code GROUP BY villagedm ) a ORDER BY dbl desc )a   ";

                }
                if (code.Length == 12)
                {
                    sql = @" select    name,code,CASE WHEN dbl > 100 THEN 100.00  when dbl is null then 0.00 ELSE dbl END dbl  from  (select village name,villagedm code,case when  round(ydb*1.00*100/zcount,2) is null then 0.00 else round(ydb*1.00*100/zcount,2) end   dbl from  (select max(village) village,villagedm,case when  sum(ssltdbfjsl) is null then 0.00 else  sum(ssltdbfjsl) end ydb ,sum(zcount) zcount  from  jcjbxxcjview where villagedm like @code GROUP BY villagedm ) a ORDER BY dbl desc )a   ";
                }

                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).ToList();
                //Dictionary<string, object> dictionary = new Dictionary<string, object>();
                //if (data != null)
                //{
                //    var properties = data as IDictionary<string, object>;
                //    foreach (var kvp in properties)
                //    {
                //        var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
                //        var list = new List<string>();
                //        foreach (var item in stringlist)
                //        {
                //            list.Add(item);
                //        }
                //        dictionary.Add(kvp.Key, list);
                //    }
                //    var r = new RespRet<object>(true, "Ok", dictionary);
                //    WriteSystemLog("houses/excute", r, null);
                //    return r;
                //}
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("houses/excute", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("houses/excute", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }




        /// <summary>
        ///人员疏散通道二级页面-人员疏散通道建设（正在施工、改造完成、正在制定方案）
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/rysstdjspie")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> RysstdJs([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
          
            string tbsql = @$" ";
            string sql = @" ";
            if (code.Length == 3)
            {
                tbsql = @$" 	select  sum(sstdywc) value, '改造完成' name from jcjbxxgqview  
					 union all
				select  sum(zzsggz) value , '正在施工' name  from jcjbxxgqview 
				   union all
				select  sum(zzzdfa) value , '制定方案' name from jcjbxxgqview 	  ";
                sql = @" select  sum(sstdywc) sstdywc,sum(zzsggz) zzsggz,sum(zzzdfa) zzzdfa from jcjbxxgqview  ";
            }
            if (code.Length == 6)
            {
                tbsql = @$"  select  sum(sstdywc) value, '改造完成' name from jcjbxxgqview  where xzqdm like @code
					 union all
				select  sum(zzsggz) value , '正在施工' name  from jcjbxxgqview where xzqdm like @code
				   union all
				select  sum(zzzdfa) value , '制定方案' name from jcjbxxgqview 	 where xzqdm like @code
 	  ";
                sql = @" select  sum(sstdywc) sstdywc,sum(zzsggz) zzsggz,sum(zzzdfa) zzzdfa from jcjbxxgqview where xzqdm like @code  ";
            }

            if (code.Length == 9)
            {
                tbsql = @$"  select  sum(sstdywc) value, '改造完成' name from jcjbxxxzview  where towndm like @code
					 union all
				select  sum(zzsggz) value , '正在施工' name  from jcjbxxxzview where towndm like @code
				   union all
				select  sum(zzzdfa) value , '制定方案' name from jcjbxxxzview 	 where towndm like @code
 	  ";
                sql = @" select  sum(sstdywc) sstdywc,sum(zzsggz) zzsggz,sum(zzzdfa) zzzdfa from jcjbxxxzview where towndm like @code  ";

            }
            if (code.Length == 12)
            {
                tbsql = @$"  select  sum(sstdywc) value, '改造完成' name from jcjbxxcjview  where villagedm like @code
					 union all
				select  sum(zzsggz) value , '正在施工' name  from jcjbxxcjview where villagedm like @code
				   union all
				select  sum(zzzdfa) value , '制定方案' name from jcjbxxcjview 	 where villagedm like @code
 	  ";
                sql = @" select  sum(sstdywc) sstdywc,sum(zzsggz) zzsggz,sum(zzzdfa) zzzdfa from jcjbxxcjview where villagedm like @code  ";
            }

            var tb = _dbContext.Database.GetDbConnection().Query<object>(tbsql, new { @code = "%" + code + "%" }).ToList();
            var msg = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).FirstOrDefault();
            return new RespRet<object>(true, "Ok", new { tb = tb, msg = msg });
        }



        /// <summary>
        ///人员疏散通道二级页面-人员疏散通道建设（已完成）（增设疏散楼梯，利用相邻相近建筑疏散）
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/rysstdjswcpie")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> rysstdjswcpie([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
           
            string tbsql = @$" ";
            string sql = @" ";
            if (code.Length == 3)
            {
                tbsql = @$" 	select  sum(jzswhntjzssslt) value, '建筑室外或内天井增设疏散楼梯' name from jcjbxxgqview  
					 union all
				select  sum(jzsnzssslt) value , '建筑室内增设疏散楼梯' name  from jcjbxxgqview 
				   union all
				select  sum(lyxlhxjjzss) value , '利用相邻或相近建筑疏散' name from jcjbxxgqview	  ";
                sql = @"  select  sum(jzswhntjzssslt) jzswhntjzssslt,sum(jzsnzssslt) jzsnzssslt,sum(lyxlhxjjzss) lyxlhxjjzss from jcjbxxgqview  ";
            }
            if (code.Length == 6)
            {
                tbsql = @$"  select  sum(jzswhntjzssslt) value, '建筑室外或内天井增设疏散楼梯' name from jcjbxxgqview  where xzqdm like @code
					 union all
				select  sum(jzsnzssslt) value , '建筑室内增设疏散楼梯' name  from jcjbxxgqview where xzqdm like @code
				   union all
				select  sum(lyxlhxjjzss) value , '利用相邻或相近建筑疏散' name from jcjbxxgqview where xzqdm like @code
 	  ";
                sql = @" select  sum(jzswhntjzssslt) jzswhntjzssslt,sum(jzsnzssslt) jzsnzssslt,sum(lyxlhxjjzss) lyxlhxjjzss from jcjbxxgqview where xzqdm like @code  ";
            }

            if (code.Length == 9)
            {
                tbsql = @$"  select  sum(jzswhntjzssslt) value, '建筑室外或内天井增设疏散楼梯' name from jcjbxxxzview   where towndm like @code
					 union all
				select  sum(jzsnzssslt) value , '建筑室内增设疏散楼梯' name  from jcjbxxxzview  where towndm like @code
				   union all
				select  sum(lyxlhxjjzss) value , '利用相邻或相近建筑疏散' name from jcjbxxxzview where towndm like @code
 	  ";
                sql = @" select  sum(jzswhntjzssslt) jzswhntjzssslt,sum(jzsnzssslt) jzsnzssslt,sum(lyxlhxjjzss) lyxlhxjjzss from jcjbxxxzview where towndm like @code  ";

            }
            if (code.Length == 12)
            {
                tbsql = @$"  select  sum(jzswhntjzssslt) value, '建筑室外或内天井增设疏散楼梯' name from jcjbxxcjview   where villagedm like @code
					 union all
				select  sum(jzsnzssslt) value , '建筑室内增设疏散楼梯' name  from jcjbxxcjview  where villagedm like @code
				   union all
				select  sum(lyxlhxjjzss) value , '利用相邻或相近建筑疏散' name from jcjbxxcjview where villagedm like @code
 	  ";
                sql = @" select  sum(jzswhntjzssslt) jzswhntjzssslt,sum(jzsnzssslt) jzsnzssslt,sum(lyxlhxjjzss) lyxlhxjjzss from jcjbxxcjview where villagedm like @code  ";
            }

            var tb = _dbContext.Database.GetDbConnection().Query<object>(tbsql, new { @code = "%" + code + "%" }).ToList();
            var msg = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).FirstOrDefault();
            return new RespRet<object>(true, "Ok", new { tb = tb, msg = msg });
        }


        /// <summary>
        /// 窗口护栏拆除zhuzhuangtu
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/ckhlccbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> ckhlccbar([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
          
            string sql = @" ";
            if (code.Length == 3)
            {
                sql = @"  select name , code,CASE WHEN ccl > 100 THEN 100.00  when ccl is null then 0.00 ELSE ccl END  ccl from (	select  code,name,case when ckfhwggptzlsl is null then 0 else ckfhwggptzlsl end ckfhwggptzlsl,case when ccfhwggptzlsl is null then 0.00 else ccfhwggptzlsl end ccfhwggptzlsl  ,
			case when 	round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) is null then 0.00 else round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) end  ccl  from (select xzqdm code,max(xzq) name, case when sum(ckfhwggptzlsl)=0 then null else  sum(ckfhwggptzlsl) end ckfhwggptzlsl from  jcjbxxgqview GROUP BY xzqdm) a left join (select xzqdm,sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj GROUP BY xzqdm)  b	 on a.code=b.xzqdm)a ORDER BY ccl desc     
           		";
            }
            if (code.Length == 6)
            {
                sql = @" select name , code,CASE WHEN ccl > 100 THEN 100.00  when ccl is null then 0.00 ELSE ccl END  ccl from (				
					select   code,name,case when ckfhwggptzlsl is null then 0.00 else ckfhwggptzlsl end ckfhwggptzlsl,case when ccfhwggptzlsl is null then 0.00 else ccfhwggptzlsl end ccfhwggptzlsl  ,
			case when 	round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) is null then 0.00 else round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) end  ccl  from (select  towndm code,max(town) name,case when sum(ckfhwggptzlsl)=0 then null else  sum(ckfhwggptzlsl) end ckfhwggptzlsl from  jcjbxxxzview where towndm like @code group by towndm) a left join  (select towndm,sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where towndm like @code group by towndm)  b on a.code=b.towndm )a ORDER BY ccl desc    ";
            }

            if (code.Length == 9)
            {
                sql = @"  select name , code,CASE WHEN ccl > 100 THEN 100.00  when ccl is null then 0.00 ELSE ccl END  ccl from (				
					select   code,name,case when ckfhwggptzlsl is null then 0.00 else ckfhwggptzlsl end ckfhwggptzlsl,case when ccfhwggptzlsl is null then 0.00 else ccfhwggptzlsl end ccfhwggptzlsl  ,
			case when 	round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) is null then 0.00 else round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) end  ccl  from (select  villagedm code,max(village) name,case when sum(ckfhwggptzlsl)=0 then null else  sum(ckfhwggptzlsl) end ckfhwggptzlsl from  jcjbxxcjview where villagedm like @code group by villagedm) a left join  (select villagedm,sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where villagedm like @code group by villagedm)  b on a.code=b.villagedm )a ORDER BY ccl desc    ";
            }
            if (code.Length == 12)
            {
                sql = @"  select name , code,CASE WHEN ccl > 100 THEN 100.00  when ccl is null then 0.00 ELSE ccl END  ccl from (				
					select   code,name,case when ckfhwggptzlsl is null then 0.00 else ckfhwggptzlsl end ckfhwggptzlsl,case when ccfhwggptzlsl is null then 0.00 else ccfhwggptzlsl end ccfhwggptzlsl  ,
			case when 	round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) is null then 0.00 else round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) end  ccl  from (select  villagedm code,max(village) name,case when sum(ckfhwggptzlsl)=0 then null else  sum(ckfhwggptzlsl) end ckfhwggptzlsl from  jcjbxxcjview where villagedm like @code group by villagedm) a left join  (select villagedm,sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where villagedm like @code group by villagedm)  b on a.code=b.villagedm )a ORDER BY ccl desc   
           ";
            }
            var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).ToList();
            //Dictionary<string, object> dictionary = new Dictionary<string, object>();
            //if (data != null)
            //{
            //    var properties = data as IDictionary<string, object>;
            //    foreach (var kvp in properties)
            //    {
            //        var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
            //        var list = new List<string>();
            //        foreach (var item in stringlist)
            //        {
            //            list.Add(item);
            //        }
            //        dictionary.Add(kvp.Key, list);
            //    }
            //    var r = new RespRet<object>(true, "Ok", dictionary);
            //    WriteSystemLog("housesjc/ckhlccbar", r, code);
            //    return r;
            //}
            return new RespRet<object>(true, "Ok", data);
        }


        /// <summary>
        /// 窗口护栏拆除
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/ckhlccmsg")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> ckhlccmsg([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
           
            string sql = @" ";
            if (code.Length == 3)
            {
                sql = @" 	select case when ckfhwggptzlsl is null then 0.00 else ckfhwggptzlsl end ckfhwggptzlsl,case when ccfhwggptzlsl is null then 0.00 else ccfhwggptzlsl end ccfhwggptzlsl  ,
			case when 	round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) is null then 0.00 else round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) end  ccl  from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxgqview) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj)  b
			 ";
            }
            if (code.Length == 6)
            {
                sql = @" select case when ckfhwggptzlsl is null then 0.00 else ckfhwggptzlsl end ckfhwggptzlsl,case when ccfhwggptzlsl is null then 0.00 else ccfhwggptzlsl end ccfhwggptzlsl  ,
			case when 	round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) is null then 0.00 else round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) end  ccl  from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxgqview where xzqdm like @code) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where xzqdm like @code)  b ";
            }
            if (code.Length == 9)
            {
                sql = @" select  case when ckfhwggptzlsl is null then 0.00 else ckfhwggptzlsl end ckfhwggptzlsl,case when ccfhwggptzlsl is null then 0.00 else ccfhwggptzlsl end ccfhwggptzlsl  ,
			case when 	round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) is null then 0.00 else round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) end  ccl  from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxxzview where towndm like @code) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where towndm like @code)  b ";
            }
            if (code.Length == 12)
            {
                sql = @" 	select case when ckfhwggptzlsl is null then 0.00 else ckfhwggptzlsl end ckfhwggptzlsl,case when ccfhwggptzlsl is null then 0.00 else ccfhwggptzlsl end ccfhwggptzlsl  ,
			case when 	round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) is null then 0.00 else round(ccfhwggptzlsl*1.0*100/ckfhwggptzlsl,2) end  ccl  from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxcjview where villagedm like @code) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where villagedm like @code)  b ";
            }
            var msg = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).FirstOrDefault();
            return new RespRet<object>(true, "Ok", msg);
        }


        /// <summary>
        ///以防护栏G个数为单位统计
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/fhlgspie")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> fhlgspie([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
           
            string sql = @" ";
            if (code.Length == 3)
            {
                sql = @" 	select case when value is null then 0.00 else value end value ,name from(		select ccfhwggptzlsl value,'已拆除' name   from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxgqview) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj)  b
			 union all
			select (ckfhwggptzlsl-ccfhwggptzlsl) value, '剩余'  from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxgqview) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj)  b
				)a ";
            }
            if (code.Length == 6)
            {
                sql = @" 	select case when value is null then 0.00 else value end value ,name from(select ccfhwggptzlsl value,'已拆除' name   from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxgqview where xzqdm like @code) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where xzqdm like @code )  b
			 union all
			select (ckfhwggptzlsl-ccfhwggptzlsl) value, '剩余'  from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxgqview where xzqdm like @code) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where xzqdm like @code )  b
			)a ";
            }
            if (code.Length == 9)
            {
                sql = @" select case when value is null then 0.00 else value end value ,name from  (select ccfhwggptzlsl value,'已拆除' name   from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxxzview where towndm like @code) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where towndm like @code )  b
			 union all
			select (ckfhwggptzlsl-ccfhwggptzlsl) value, '剩余'  from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxxzview where towndm like @code) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where towndm like @code )  b
			)a ";
            }
            if (code.Length == 12)
            {
                sql = @" select case when value is null then 0.00 else value end value ,name from(	select ccfhwggptzlsl value,'已拆除' name   from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxcjview where villagedm like @code) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where villagedm like @code )  b
			 union all
			select (ckfhwggptzlsl-ccfhwggptzlsl) value, '剩余'  from (select  sum(ckfhwggptzlsl)ckfhwggptzlsl from  jcjbxxcjview where villagedm like @code) a,(select sum(ccfhwggptzlsl) ccfhwggptzlsl from zm_pc_gjzz_cj where villagedm like @code )  b
			)a ";

            }
            var tb = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).ToList();
            return new RespRet<object>(true, "Ok", tb);
        }

        /// <summary>
        ///以防护栏G个数为单位统计
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/fhlfzdspie")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> Fhlfzdspie([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
           
            string sql = @" ";
            if (code.Length == 3)
            {
                sql = @" select  count(case when  ckfhwggptzlsl>0  then 1 end)-(select count( case when ccfhwggptzlsl>0 then 1 end )value  from zm_pc_gjzz_cj) value,'剩余'name from  zm_pc_base_cj
		union all 
		select count( case when ccfhwggptzlsl>0 then 1 end )value ,'已拆除' name from zm_pc_gjzz_cj ";
            }
            if (code.Length == 6)
            {
                sql = @" 	select  count(case when  ckfhwggptzlsl>0  then 1 end)-(select count( case when ccfhwggptzlsl>0 then 1 end )value  from zm_pc_gjzz_cj where xzqdm like @code ) value,'剩余'name from  zm_pc_base_cj where xzqdm like @code
		union all 
		select count( case when ccfhwggptzlsl>0 then 1 end )value ,'已拆除' name from zm_pc_gjzz_cj where xzqdm like @code  ";
            }
            if (code.Length == 9)
            {
                sql = @" 	select  count(case when  ckfhwggptzlsl>0  then 1 end)-(select count( case when ccfhwggptzlsl>0 then 1 end )value  from zm_pc_gjzz_cj where towndm like @code ) value,'剩余'name from  zm_pc_base_cj where xzqdm like @code
		union all 
		select count( case when ccfhwggptzlsl>0 then 1 end )value ,'已拆除' name from zm_pc_gjzz_cj where towndm like @code  ";
            }
            if (code.Length == 12)
            {
                sql = @" 		select  count(case when  ckfhwggptzlsl>0  then 1 end)-(select count( case when ccfhwggptzlsl>0 then 1 end )value  from zm_pc_gjzz_cj where villagedm like @code ) value,'剩余'name from  zm_pc_base_cj where xzqdm like @code
		union all 
		select count( case when ccfhwggptzlsl>0 then 1 end )value ,'已拆除' name from zm_pc_gjzz_cj where villagedm like @code ";

            }
            var tb = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).ToList();
            return new RespRet<object>(true, "Ok", tb);
        }



        /// <summary>
        /// 电动自行车违规充电
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/ddzxcwgcdmsg")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> ddzxcwgcdmsg([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
            string sql = @$" 
  select sum(zs)zs,sum(qlddcsl) qlddcsl,  case when  round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2) is null then 0.00 when round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2)>100 then 100.00 else  round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2) end wcl from ( select  region_name,case when zs is null then 0 else zs end zs,case when qlddcsl is null then 0 else qlddcsl end qlddcsl   from   (select bdfwjzrydddzxcsl zs,xzqdm region_code from jcjbxxgqview)a
join 
(select  sum(zxqlddcsl) qlddcsl,c.region_code,max(c.region_name) region_name from zm_pc_yhlog_cj a left join zm_buildings b on a.fwbh=b.fwbh RIGHT JOIN (select xzqdm region_code,max(xzq) region_name from  zm_building_ledger where  xzq not in ('东城区','西城区')GROUP BY xzqdm ) c on b.xzqdm=c.region_code  GROUP BY c.region_code ORDER BY c.region_code) b on a.region_code=b.region_code) a
";

            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
            var lea =RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Length - 3 : RegionCode.Length;
            if (lea == 6)
            {
                sql = @$" select sum(zs)zs,sum(qlddcsl) qlddcsl,  case when  round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2) is null then 0.00 when round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2)>100 then 100.00 else  round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2) end wcl from ( select  region_name,case when zs is null then 0 else zs end zs,case when qlddcsl is null then 0 else qlddcsl end qlddcsl   from   (select bdfwjzrydddzxcsl zs,xzqdm region_code from jcjbxxgqview where xzqdm like @code)a
join 
(select  sum(zxqlddcsl) qlddcsl,c.region_code,max(c.region_name) region_name from zm_pc_yhlog_cj a left join zm_buildings b on a.fwbh=b.fwbh RIGHT JOIN (select xzqdm region_code,max(xzq) region_name from  zm_building_ledger where  xzq not in ('东城区','西城区')GROUP BY xzqdm ) c on b.xzqdm=c.region_code  GROUP BY c.region_code ORDER BY c.region_code) b on a.region_code=b.region_code where a.region_code like @code) a
  ";
            }
            if (lea == 9)
            {
                sql = @$"   select sum(zs)zs,sum(qlddcsl) qlddcsl,  case when  round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2) is null then 0.00 when round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2)>100 then 100.00 else  round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2) end wcl from ( select  region_name,case when zs is null then 0 else zs end zs,case when qlddcsl is null then 0 else qlddcsl end qlddcsl   from   (select bdfwjzrydddzxcsl zs,towndm region_code from jcjbxxxzview where towndm like @code)a
join 
(select  sum(zxqlddcsl) qlddcsl,c.region_code,max(c.region_name) region_name from zm_pc_yhlog_cj a left join zm_buildings b on a.fwbh=b.fwbh RIGHT JOIN (select towndm region_code,max(xzq) region_name from  zm_building_ledger where  xzq not in ('东城区','西城区')GROUP BY towndm ) c on b.towndm=c.region_code  GROUP BY c.region_code ORDER BY c.region_code) b on a.region_code=b.region_code where a.region_code like @code) a ";
            }
            if (lea == 12)
            {
                sql = @$" select sum(zs)zs,sum(qlddcsl) qlddcsl,  case when  round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2) is null then 0.00 when round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2)>100 then 100.00 else  round(sum(qlddcsl)*1.00*100/case when sum(zs)=0 then null else sum(zs) end,2) end wcl from ( select  region_name,case when zs is null then 0 else zs end zs,case when qlddcsl is null then 0 else qlddcsl end qlddcsl   from   (select bdfwjzrydddzxcsl zs,villagedm region_code from jcjbxxcjview where villagedm like @code)a
join 
(select  sum(zxqlddcsl) qlddcsl,c.region_code,max(c.region_name) region_name from zm_pc_yhlog_cj a left join zm_buildings b on a.fwbh=b.fwbh RIGHT JOIN (select villagedm region_code,max(xzq) region_name from  zm_building_ledger where  xzq not in ('东城区','西城区')GROUP BY villagedm ) c on b.villagedm=c.region_code  GROUP BY c.region_code ORDER BY c.region_code) b on a.region_code=b.region_code where a.region_code like @code) a  ";
            }
            var msg = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).FirstOrDefault();
            return new RespRet<object>(true, "Ok", msg);
        }

        /// <summary>
        /// 清理室内电动自行车情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/ddzxcwgcdbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> Ddzxcwgcdbar(string code)
        {
            try
            {
                var usernNumber = this.CurrentUser();
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
                if (string.IsNullOrWhiteSpace(code))
                {
                    code = RegionCode;
                }
                code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
                
                if (string.IsNullOrWhiteSpace(code))
                {
                    code = "110000";
                }
                string sql = @$" 
    select CONCAT('[', STRING_AGG(region_code, ', '), ']') AS code,CONCAT('[', STRING_AGG(region_name, ', '), ']') AS x,CONCAT('[', STRING_AGG(CAST(zs AS VARCHAR), ', ' ), ']') AS zs,CONCAT('[', STRING_AGG(CAST(qlddcsl AS VARCHAR), ', ' ), ']') AS qlddcsl from ( select a.region_code, region_name,case when zs is null then 0.00 else zs end zs,case when qlddcsl is null then 0.00 else qlddcsl end qlddcsl   from   (select bdfwjzrydddzxcsl zs,xzqdm region_code from jcjbxxgqview)a
join 
(select  sum(zxqlddcsl) qlddcsl,c.region_code,max(c.region_name) region_name from zm_pc_yhlog_cj a left join zm_buildings b on a.fwbh=b.fwbh RIGHT JOIN (select xzqdm region_code,max(xzq) region_name from  zm_building_ledger where  xzq not in ('东城区','西城区')GROUP BY xzqdm ) c on b.xzqdm=c.region_code  GROUP BY c.region_code ORDER BY c.region_code) b on a.region_code=b.region_code) a

";

                code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
                  var lea =RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Length - 3 : RegionCode.Length;
                if (lea == 6)
                {
                    sql = @$" select CONCAT('[', STRING_AGG(region_code, ', '), ']') AS code,CONCAT('[', STRING_AGG(region_name, ', '), ']') AS x,CONCAT('[', STRING_AGG(CAST(zs AS VARCHAR), ', ' ), ']') AS zs,CONCAT('[', STRING_AGG(CAST(qlddcsl AS VARCHAR), ', ' ), ']') AS qlddcsl from ( select  a.region_code,region_name,case when zs is null then 0.00 else zs end zs,case when qlddcsl is null then 0.00 else qlddcsl end qlddcsl   from   (select  bdfwjzrydddzxcsl zs,towndm region_code from jcjbxxxzview)a
join 
(select  sum(zxqlddcsl) qlddcsl,c.region_code,max(c.region_name) region_name from zm_pc_yhlog_cj a left join zm_buildings b on a.fwbh=b.fwbh RIGHT JOIN (select towndm region_code,max(town) region_name from  zm_building_ledger where towndm like '%110105%'  GROUP BY towndm ) c on b.towndm=c.region_code  GROUP BY c.region_code ORDER BY c.region_code) b on a.region_code=b.region_code) a
          ";
                }
                if (lea == 9)
                {
                    sql = @$"select  CONCAT('[', STRING_AGG(region_code, ', '), ']') AS code,CONCAT('[', STRING_AGG(region_name, ', '), ']') AS x,CONCAT('[', STRING_AGG(CAST(zs AS VARCHAR), ', ' ), ']') AS zs,CONCAT('[', STRING_AGG(CAST(qlddcsl AS VARCHAR), ', ' ), ']') AS qlddcsl from ( select region_code, region_name,case when zs is null then 0.00 else zs end zs,case when qlddcsl is null then 0.00 else qlddcsl end qlddcsl   from   (select bdfwjzrydddzxcsl zs,villagedm region_code from jcjbxxcjview)a
join 
(select  sum(zxqlddcsl) qlddcsl,c.villagedm,max(c.village) region_name from zm_pc_yhlog_cj a left join zm_buildings b on a.fwbh=b.fwbh RIGHT JOIN  zm_building_ledger c  on b.villagedm=c.villagedm  where c.villagedm like @code GROUP BY c.villagedm ORDER BY c.villagedm) b on a.region_code=b.villagedm) a
              ";
                }
                if (lea == 12)
                {
                    sql = @$" select  CONCAT('[', STRING_AGG(region_code, ', '), ']') AS code,CONCAT('[', STRING_AGG(region_name, ', '), ']') AS x,CONCAT('[', STRING_AGG(CAST(zs AS VARCHAR), ', ' ), ']') AS zs,CONCAT('[', STRING_AGG(CAST(qlddcsl AS VARCHAR), ', ' ), ']') AS qlddcsl from ( select region_code, region_name,case when zs is null then 0.00 else zs end zs,case when qlddcsl is null then 0.00 else qlddcsl end qlddcsl   from   (select bdfwjzrydddzxcsl zs,villagedm region_code from jcjbxxcjview)a
join 
(select  sum(zxqlddcsl) qlddcsl,c.villagedm,max(c.village) region_name from zm_pc_yhlog_cj a left join zm_buildings b on a.fwbh=b.fwbh RIGHT JOIN  zm_building_ledger c  on b.villagedm=c.villagedm  where c.villagedm like @code GROUP BY c.villagedm ORDER BY c.villagedm) b on a.region_code=b.villagedm) a
              ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).FirstOrDefault();
                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                if (data != null)
                {
                    var properties = data as IDictionary<string, object>;
                    foreach (var kvp in properties)
                    {
                        var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
                        var list = new List<string>();
                        foreach (var item in stringlist)
                        {
                            list.Add(item);
                        }
                        dictionary.Add(kvp.Key, list);
                    }
                    var r = new RespRet<object>(true, "Ok", dictionary);
                    WriteSystemLog("housesjc/ddzxcwgcdbar", r, code);
                    return r;
                }
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housesjc/ddzxcwgcdbar", res, code);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString(), code);
                WriteSystemLogError("houses/qlsnddzxcqk", res, code);
                res.Msg = "系统异常";
                return res;
            }
        }




        /// <summary>
        /// 违规使用液化石油气
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/wgsyyhsyqmsg")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> Wgsyyhsyqmsg([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
      
            string sql = @$" 
   select fx ,zg ,     case when fx=0 then 0.00 else (case when ROUND(zg*1.00*100/fx,2)>100 then 100.00 when ROUND(zg*1.00*100/fx,2) is null then 0.00 else ROUND(zg*1.00*100/fx,2) end) end wcl  From (select sum( case when (a.use_bottled_liquefied_gas+b.use_bottled_liquefied_gas)=0 then null else (a.use_bottled_liquefied_gas+b.use_bottled_liquefied_gas) end) fx from  zcyhgqview a,jcyhgqview b where a.xzqdm=b.xzqdm  )a,(select  COALESCE(zc,0)+  COALESCE(jc,0) zg from  (select sum(zg_yhsyq_num) zc from zm_pc_yhlog_dxzgson a,zm_building_ledger b where a.villagedm=b.villagedm   ) a,  (select sum(zg_yhsyq_num) jc from zm_pc_yhlog_dxzgson_cj a,zm_building_ledger b where a.villagedm=b.villagedm    ) b)b     ";

            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
              var lea =RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Length - 3 : RegionCode.Length;
            if (lea == 6)
            {
                sql = @$" select fx ,zg ,     case when fx=0 then 0.00 else (case when ROUND(zg*1.00*100/fx,2)>100 then 100.00 when ROUND(zg*1.00*100/fx,2) is null then 0.00 else ROUND(zg*1.00*100/fx,2) end) end wcl  From (select sum( case when (a.use_bottled_liquefied_gas+b.use_bottled_liquefied_gas)=0 then null else (a.use_bottled_liquefied_gas+b.use_bottled_liquefied_gas) end) fx from  zcyhgqview a,jcyhgqview b where a.xzqdm=b.xzqdm and a.xzqdm like @code)a,(select  COALESCE(zc,0)+  COALESCE(jc,0) zg from  (select sum(zg_yhsyq_num) zc from zm_pc_yhlog_dxzgson a,zm_building_ledger b where a.village=b.village and a.xzqdm like @code ) a,  (select sum(zg_yhsyq_num) jc from zm_pc_yhlog_dxzgson_cj a,zm_building_ledger b where a.villagedm=b.villagedm  and a.xzqdm like @code ) b)b 
           ";
            }
            if (lea == 9)
            {
                sql = @$" select fx ,zg ,     case when fx=0 then 0.00 else (case when ROUND(zg*1.00*100/fx,2)>100 then 100.00 when ROUND(zg*1.00*100/fx,2) is null then 0.00 else ROUND(zg*1.00*100/fx,2) end) end wcl  From (select sum( case when (a.use_bottled_liquefied_gas+b.use_bottled_liquefied_gas)=0 then null else (a.use_bottled_liquefied_gas+b.use_bottled_liquefied_gas) end) fx from  zcyhxzview a,jcyhxzview b where a.towndm=b.towndm and a.towndm like @code)a,(select  COALESCE(zc,0)+  COALESCE(jc,0) zg from  (select sum(zg_yhsyq_num) zc from zm_pc_yhlog_dxzgson a,zm_building_ledger b where a.villagedm=b.villagedm and a.towndm like @code ) a,  (select sum(zg_yhsyq_num) jc from zm_pc_yhlog_dxzgson_cj a,zm_building_ledger b where a.villagedm=b.villagedm  and a.towndm like @code ) b)b  ";   }
            if (lea == 12)
            {

                sql = @$" select fx ,zg ,     case when fx=0 then 0.00 else (case when ROUND(zg*1.00*100/fx,2)>100 then 100.00 when ROUND(zg*1.00*100/fx,2) is null then 0.00 else ROUND(zg*1.00*100/fx,2) end) end wcl  From (select sum( case when (a.use_bottled_liquefied_gas+b.use_bottled_liquefied_gas)=0 then null else (a.use_bottled_liquefied_gas+b.use_bottled_liquefied_gas) end) fx from  zcyhcjview a,jcyhcjview b where a.villagedm=b.villagedm and a.villagedm like @code)a,(select  COALESCE(zc,0)+  COALESCE(jc,0) zg from  (select sum(zg_yhsyq_num) zc from zm_pc_yhlog_dxzgson a,zm_building_ledger b where a.villagedm=b.villagedm and a.villagedm like @code ) a,  (select sum(zg_yhsyq_num) jc from zm_pc_yhlog_dxzgson_cj a,zm_building_ledger b where a.villagedm=b.villagedm  and a.villagedm like @code ) b)b    "; }
            var msg = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).FirstOrDefault();
            return new RespRet<object>(true, "Ok", msg);
        }

        /// <summary>
        /// 违规使用液化石油气
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/wgsyyhsyqbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> WgsyyhsyqBar([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
         
            string sql = @$" 
     select CONCAT('[', STRING_AGG(xzq, ', '), ']') AS name,CONCAT('[', STRING_AGG(CAST(b.xzqdm AS VARCHAR), ', ' ), ']') AS code,CONCAT('[', STRING_AGG(CAST(zxqlyhsyqsl AS VARCHAR), ', ' ), ']') AS zxqlyhsyqsl,CONCAT('[', STRING_AGG(use_bottled_liquefied_gas ::text, ', '), ']') AS use_bottled_liquefied_gas from (select use_bottled_liquefied_gas,xzqdm from jcyhgqview)a,  
		(select max(b.xzq)xzq ,case when   sum(zxqlyhsyqsl) is null then 0.00 else sum(zxqlyhsyqsl) end  zxqlyhsyqsl,b.xzqdm  from zm_pc_yh_cj a RIGHT JOIN zm_building_ledger b on a.villagedm=b.villagedm group by b.xzqdm ORDER BY b.xzqdm)b where a.xzqdm=b.xzqdm
		
		 ";

            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
              var lea =RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Length - 3 : RegionCode.Length;
            if (lea == 6)
            {
                sql = @$"     select CONCAT('[', STRING_AGG(town, ', '), ']') AS name,CONCAT('[', STRING_AGG(CAST(b.towndm AS VARCHAR), ', ' ), ']') AS code,CONCAT('[', STRING_AGG(CAST(zxqlyhsyqsl AS VARCHAR), ', ' ), ']') AS zxqlyhsyqsl,CONCAT('[', STRING_AGG(use_bottled_liquefied_gas ::text, ', '), ']') AS use_bottled_liquefied_gas from

(select use_bottled_liquefied_gas,towndm from jcyhxzview where towndm like @code )a,  
		(select max(b.town)town ,case when   sum(zxqlyhsyqsl) is null then 0.00 else sum(zxqlyhsyqsl) end  zxqlyhsyqsl,b.towndm  from zm_pc_yh_cj a RIGHT JOIN zm_building_ledger b on a.villagedm=b.villagedm where b.towndm like @code group by b.towndm ORDER BY b.towndm)b where a.towndm=b.towndm 
		
		 ";
            }
            if (lea == 9)
            {
                sql = @$" select CONCAT('[', STRING_AGG(village, ', '), ']') AS name,CONCAT('[', STRING_AGG(CAST(b.villagedm AS VARCHAR), ', ' ), ']') AS code,CONCAT('[', STRING_AGG(CAST(zxqlyhsyqsl AS VARCHAR), ', ' ), ']') AS zxqlyhsyqsl,CONCAT('[', STRING_AGG(use_bottled_liquefied_gas ::text, ', '), ']') AS use_bottled_liquefied_gas from

(select use_bottled_liquefied_gas,villagedm from jcyhcjview where villagedm like @code )a,  
		(select max(b.village)village ,case when   sum(zxqlyhsyqsl) is null then 0.00 else sum(zxqlyhsyqsl) end  zxqlyhsyqsl,b.villagedm  from zm_pc_yh_cj a RIGHT JOIN zm_building_ledger b on a.villagedm=b.villagedm where b.villagedm like @code group by b.villagedm ORDER BY b.villagedm)b where a.villagedm=b.villagedm  ";
            }
            if (lea == 12)
            {
                sql = @$" select CONCAT('[', STRING_AGG(village, ', '), ']') AS name,CONCAT('[', STRING_AGG(CAST(b.villagedm AS VARCHAR), ', ' ), ']') AS code,CONCAT('[', STRING_AGG(CAST(zxqlyhsyqsl AS VARCHAR), ', ' ), ']') AS zxqlyhsyqsl,CONCAT('[', STRING_AGG(use_bottled_liquefied_gas ::text, ', '), ']') AS use_bottled_liquefied_gas from

(select use_bottled_liquefied_gas,villagedm from jcyhcjview where villagedm like @code )a,  
		(select max(b.village)village ,case when   sum(zxqlyhsyqsl) is null then 0.00 else sum(zxqlyhsyqsl) end  zxqlyhsyqsl,b.villagedm  from zm_pc_yh_cj a RIGHT JOIN zm_building_ledger b on a.villagedm=b.villagedm where b.villagedm like @code group by b.villagedm ORDER BY b.villagedm)b where a.villagedm=b.villagedm  ";

            }
            var msg = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).FirstOrDefault();
            Dictionary<string, object> dictionary = new Dictionary<string, object>();
            if (msg != null)
            {
                var properties = msg as IDictionary<string, object>;
                foreach (var kvp in properties)
                {
                    var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
                    var list = new List<string>();
                    foreach (var item in stringlist)
                    {
                        list.Add(item);
                    }
                    dictionary.Add(kvp.Key, list);
                }
                var r = new RespRet<object>(true, "Ok", dictionary);
                WriteSystemLog("housesjc/wgsyyhsyqbar", r, code);
                return r;
            }
            var res = new RespRet<object>(true, "Ok", msg);
            WriteSystemLog("housesjc/wgsyyhsyqbar", res, code);
            return new RespRet<object>(true, "Ok", msg);
        }



        /// <summary>
        /// 固定警示标牌
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/gdjsbt")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> gdjsbt([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
           
            string tbsql = @$" ";
            string sql = @" ";
            if (code.Length == 3)
            {
                tbsql = @$" select sum(xggdjsbp) xggdjsbp,round(sum(xggdjsbp)*1.00*100/sum(zcount),2) azl from  jcjbxxgqview  ";
                sql = @"   select round(sum(xggdjsbp)*1.00*100/sum(zcount),2) value ,'已安装' name from  jcjbxxgqview
 union all 
  select 100 - round(sum(xggdjsbp)*1.00*100/sum(zcount),2) value,'未安装'name from  jcjbxxgqview ";
            }
            if (code.Length == 6)
            {
                tbsql = @$" select sum(xggdjsbp) xggdjsbp,round(sum(xggdjsbp)*1.00*100/sum(zcount),2) azl from  jcjbxxgqview where xzqdm like @code	 ";
                sql = @"   select round(sum(xggdjsbp)*1.00*100/sum(zcount),2) value ,'已安装' name from  jcjbxxgqview where xzqdm like @code
 union all 
  select 100 - round(sum(xggdjsbp)*1.00*100/sum(zcount),2) value,'未安装'name from  jcjbxxgqview  where xzqdm like @code";
            }

            if (code.Length == 9)
            {
                tbsql = @$" select sum(xggdjsbp) xggdjsbp,round(sum(xggdjsbp)*1.00*100/sum(zcount),2) azl from  jcjbxxxzview  where towndm like @code
 ";
                sql = @"   select round(sum(xggdjsbp)*1.00*100/sum(zcount),2) value ,'已安装' name from  jcjbxxxzview where towndm like @code
 union all 
  select 100 - round(sum(xggdjsbp)*1.00*100/sum(zcount),2) value,'未安装'name from  jcjbxxxzview  where towndm like @code";
            }
            if (code.Length == 12)
            {
                tbsql = @$" select sum(xggdjsbp) xggdjsbp,round(sum(xggdjsbp)*1.00*100/sum(zcount),2) azl from  jcjbxxcjview  where villagedm like @code ";
                sql = @"   select round(sum(xggdjsbp)*1.00*100/sum(zcount),2) value ,'已安装' name from  jcjbxxcjview where villagedm like @code
 union all 
  select 100 - round(sum(xggdjsbp)*1.00*100/sum(zcount),2) value,'未安装'name from  jcjbxxcjview  where villagedm like @code";
            }


            var tb = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).ToList();
            var msg = _dbContext.Database.GetDbConnection().Query<object>(tbsql, new { @code = "%" + code + "%" }).FirstOrDefault();
            return new RespRet<object>(true, "Ok", new { tb = tb, msg = msg });
        }



        /// <summary>
        /// 安装固定警示标牌柱状图
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("housesjc/gdjsbtbar")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> gdjsbtbar([FromQuery] string code)
        {

            var usernNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
            if (string.IsNullOrWhiteSpace(code))
            {
                code = RegionCode;
            }
            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
           
            string sql = @$" 
    select  xggdjsbp,jzfjs,code,xzq AS name,case when value >100 then 100.00 when value is null then 0.00 else value end value  from (  select jzfjs,xggdjsbp,   round(xggdjsbp*1.00*100/jzfjs,2) value ,xzq,xzqdm code from  jcjbxxgqview  ORDER BY value desc) a";

            code = code.Substring(code.Length - 3) == "000" ? code.Substring(0, code.Length - 3) : code;
              var lea =RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Length - 3 : RegionCode.Length;
            if (lea == 6)
            {
                sql = @$"   select  xggdjsbp,jzfjs,code,town AS name,case when value >100 then 100.00 when value is null then 0.00 else value end value from (  select jzfjs,xggdjsbp,round(xggdjsbp*1.00*100/jzfjs,2) value ,town,towndm code from  jcjbxxxzview where towndm like @code  ORDER BY value desc) a	 ";
            }
            if (lea == 9)
            {
                sql = @$" select  xggdjsbp,jzfjs,code,village AS name,case when value >100 then 100.00 when value is null then 0.00 else value end value from (  select jzfjs,xggdjsbp ,round(xggdjsbp*1.00*100/jzfjs,2) value ,village,villagedm code from  jcjbxxcjview where villagedm like @code  ORDER BY value desc) a
	 ";
            }
            if (lea == 12)
            {
                sql = @$" select xggdjsbp,jzfjs,code,village AS name,case when value >100 then 100.00 when value is null then 0.00 else value end value  from (  select xggdjsbp,jzfjs, round(xggdjsbp*1.00*100/jzfjs,2) value ,village,villagedm code from  jcjbxxcjview where villagedm like @code  ORDER BY value desc) a
 ";
            }
            var msg = _dbContext.Database.GetDbConnection().Query<object>(sql, new { @code = "%" + code + "%" }).ToList();
            //Dictionary<string, object> dictionary = new Dictionary<string, object>();
            //if (msg != null)
            //{
            //    var properties = msg as IDictionary<string, object>;
            //    foreach (var kvp in properties)
            //    {
            //        var stringlist = kvp.Value.ToString().Trim('[', ']').Split(", ");
            //        var list = new List<string>();
            //        foreach (var item in stringlist)
            //        {
            //            list.Add(item);
            //        }
            //        dictionary.Add(kvp.Key, list);
            //    }
            //    var r = new RespRet<object>(true, "Ok", dictionary);
            //    WriteSystemLog("housesjc/wgsyyhsyqbar", r, code);
            //    return r;
            //}
            var res = new RespRet<object>(true, "Ok", msg);
            WriteSystemLog("housesjc/wgsyyhsyqbar", res, code);
            return new RespRet<object>(true, "Ok", msg);
        }


        /// <summary>
        /// 各区自查栋数，自查次数
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjc/zcqk")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> Zcqk([FromBody] DateQuery param)
        {
            try
            {
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                var usernNumber = this.CurrentUser();
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0,RegionCode.Length - 3) : RegionCode;
                bool isxz = true;
                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;
                string sql = @" ";
                if (param.code.Length == 3 || param.code.Length == 6)
                {
                    sql = @" 
		select a.jccount zccount,a.xzqdm code,a.xzq name,case when  fwzccs is null then 0 else fwzccs end fwzccs   From jcqkgqtext(@startime,@endtime) a left join
					 (select count(1) fwzccs,zm_buildings.xzqdm from zm_pc_yhlog_cj ,zm_buildings where zm_pc_yhlog_cj.fwbh=zm_buildings.fwbh  and jcly='5' and date(create_on) between @startime and @endtime  GROUP BY zm_buildings.xzqdm) b on a.xzqdm=b.xzqdm ";
                }
                if (isxz)
                {
                    if (param.code.Length == 6)
                    {
                        sql = @" 	
 select a.jccount zccount,a.towndm code,a.town name,case when  fwzccs is null then 0 else fwzccs end fwzccs  From jcqkxztext(@startime,@endtime) a left join
						 (select count(1) fwzccs,zm_buildings.towndm from zm_pc_yhlog_cj,zm_buildings where zm_pc_yhlog_cj.fwbh=zm_buildings.fwbh and jcly='5' and date(create_on) between date(@startime) and date(@endtime)  GROUP BY zm_buildings.towndm) b on a.towndm=b.towndm where a.towndm like @code   
                   ";
                    }
                }

                if (param.code.Length == 9)
                {
                    sql = @"  select a.jccount zccount,a.villagedm code,a.village name,case when  fwzccs is null then 0 else fwzccs end fwzccs  From jcqkcjtext(@startime,@endtime) a left join
						 (select count(1) fwzccs,zm_buildings.villagedm from zm_pc_yhlog_cj,zm_buildings where zm_pc_yhlog_cj.fwbh=zm_buildings.fwbh and jcly='5' and date(create_on) between @startime and @endtime  GROUP BY zm_buildings.villagedm) b on a.villagedm=b.villagedm  where a.villagedm like @code ";
                }
                if (param.code.Length == 12)
                {
                    sql = @"  select a.jccount zccount,a.villagedm code,a.village name,case when  fwzccs is null then 0 else fwzccs end fwzccs  From jcqkcjtext(@startime,@endtime) a left join
						 (select count(1) fwzccs,zm_buildings.villagedm from zm_pc_yhlog_cj,zm_buildings where zm_pc_yhlog_cj.fwbh=zm_buildings.fwbh and jcly='5' and date(create_on) between @startime and @endtime  GROUP BY zm_buildings.villagedm) b on a.villagedm=b.villagedm  where a.villagedm like @code ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<ZcqkDto>(sql, new { @code = "%" + param.code + "%", @startime = param.date[0], @endtime = param.date[1] }).ToList();
                var zfwzccs = data.Sum(c => c.fwzccs);

                var res = new RespRet<object>(true, "Ok", new { data = data, zfwzccs = zfwzccs });
                WriteSystemLog("housesjc/zcqk", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/zcqk", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }




        /// <summary>
        /// 检查记录返回房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjc/wzcfwdetail")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<ZmPcYhLogCjDto2>>), 200)]
        public RespRet<Page<ZmPcYhLogCjDto>> wzcfwdetail([FromBody] FwlbTj pageRequest)
        {

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<ZmPcYhLogCjDto>>(false, "user not logined");
            }
            try
            {
                if (string.IsNullOrWhiteSpace(pageRequest.code))
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "村代码不能为空");
                }
                if (pageRequest.date == null || pageRequest.date.Count != 2)
                {
                    return new RespRet<Page<ZmPcYhLogCjDto>>(false, "时间不能为空");
                }
                var sbSql = new StringBuilder();
                var sbCountSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sbSqlWhere2 = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();
                pageRequest.code = pageRequest.code.Substring(pageRequest.code.Length - 3) == "000" ? pageRequest.code.Substring(0, pageRequest.code.Length - 3) : pageRequest.code;



                if (pageRequest.date[1] > DateTime.Now)
                {
                    pageRequest.date[1] = DateTime.Today;
                }
                pageRequest.date[1] = pageRequest.date[1].AddHours(23);
                pageRequest.date[1] = pageRequest.date[1].AddSeconds(59);
                pageRequest.date[1] = pageRequest.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = pageRequest.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + DateTime.Now.Year;
                sbSql.Append(@$" 
select fwbh,address,hzxm From zm_buildings  where fwbh not in ( select distinct fwbh From  zm_pc_yhlog_cj where  create_on between @startime and @endtime::TIMESTAMP and fwbh not in (select fwbh from {table_name} where final_create_on=@endtime and final_fwzt is not null)) 
and fwbh not in (select fwbh from {table_name} where final_create_on=@endtime and final_fwzt is not null)and villagedm=@code
 ");
                sbCountSql.Append(@$" 
select count(1) From zm_buildings  where fwbh not in ( select distinct fwbh From  zm_pc_yhlog_cj where  create_on between @startime and @endtime::TIMESTAMP and fwbh not in (select fwbh from {table_name} where final_create_on=@endtime and final_fwzt is not null)) 
and fwbh not in (select fwbh from {table_name} where final_create_on=@endtime and final_fwzt is not null)and villagedm=@code
 ");
                var count = _dbContext.Database.GetDbConnection().Query<int>(sbCountSql.ToString(), new { @code = pageRequest.code, @startime = pageRequest.date[0], @endtime =formattedDate }).FirstOrDefault();
                sbSql.Append($@" order by fwbh ");
                sbSql.Append($@" limit @limit offset @offset");
                sqlParams.Add(new NpgsqlParameter("code",pageRequest.code));
                sqlParams.Add(new NpgsqlParameter("limit", pageRequest.Limit));
                sqlParams.Add(new NpgsqlParameter("offset", pageRequest.Limit * pageRequest.PageIndex));
                sqlParams.Add(new NpgsqlParameter("startime", pageRequest.date[0]));
                sqlParams.Add(new NpgsqlParameter("endtime", formattedDate));
                var datas = _dbContext.ZmPcYhLogCjDto.FromSqlRaw(sbSql.ToString(), sqlParams.ToArray()).ToList();
                var pageData = new Page<ZmPcYhLogCjDto>(datas
                    , pageRequest.PageIndex + 1, pageRequest.Limit, count);
                var r = new RespRet<Page<ZmPcYhLogCjDto>>(true, "Ok", pageData);
                WriteSystemLog("housescj/search-wzcfwdetail", r, pageRequest);
                return r;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<ZmPcYhLogCjDto>>(false, ex.ToString());
                WriteSystemLogError("housescj/search-wzcfwdetail", res, pageRequest);
                res.Msg = "系统异常";
                return res;
            }
        }



        /// <summary>
        /// 发现整改隐患 统计 详情
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjc/yhdetaile")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<FwlbDto>>), 200)]
        public RespRet<Page<object>> Yhdetailes([FromBody] YhQuery pageRequest)
        {
            if (pageRequest == null)
            {
                return new RespRet<Page<object>>(false, "请传递参数");
            }
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == userNumber && c.IsDelete != 1 && c.Disabled != 1);
            var a = JsonRegion(user.District);
            JObject obj = JObject.Parse(user.District);
            string RegionCode = (string)obj["RegionCode"];
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            try
            {
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();
                sbSql.Append($@" select fwbh,create_on,check_nickname,check_name,id  from  zm_pc_yhlog_dx_cj  where create_on between @startime and @endtime and villagedm like @code    ");
                var CountSql = new StringBuilder();
                CountSql.Append($@" select count(1)  from  zm_pc_yhlog_dx_cj  where create_on between @startime and @endtime and villagedm like @code   ");
                if (string.IsNullOrWhiteSpace(pageRequest.Code))
                {
                    return new RespRet<Page<object>>(false, "行政区划代码不能为空");
                }
                if (pageRequest.Date == null || pageRequest.Date.Count != 2)
                {
                    return new RespRet<Page<object>>(false, "时间不能为空");
                }
                if (pageRequest.Type == 2)
                {
                    sbSql = new StringBuilder();
                    sbSqlWhere = new StringBuilder();
                    sqlParams = new List<NpgsqlParameter>();
                    sbSql.Append($@" select fwbh,create_on,check_nickname,check_name,id  from  zm_pc_yhlog_dx_cj  where create_on between @startime and @endtime and villagedm like @code and zg_value='1'   ");
                    CountSql = new StringBuilder();
                    CountSql.Append($@" select count(1)  from  zm_pc_yhlog_dx_cj  where create_on between @startime and @endtime and villagedm like @code  and zg_value='1' ");
                }
                sbSql.Append($@" order by fwbh");
                sbSql.Append($@" limit @limit offset @offset");
                var datas = _dbContext.Database.GetDbConnection().Query<object>(sbSql.ToString(), new { @code = "%" + pageRequest.Code + "%", @startime = pageRequest.Date[0], @endtime = pageRequest.Date[1], @limit = pageRequest.Limit, @offset = pageRequest.Limit * pageRequest.PageIndex }).ToList();
                var count = _dbContext.Database.GetDbConnection().Query<int>(CountSql.ToString(), new { @code = "%" + pageRequest.Code + "%", @startime = pageRequest.Date[0], @endtime = pageRequest.Date[1], @limit = pageRequest.Limit, @offset = pageRequest.Limit * pageRequest.PageIndex }).FirstOrDefault();
                var pageData = new RespRet<Page<object>>(true, "ok", new Page<object>(datas
                    , pageRequest.PageIndex + 1, pageRequest.Limit, count)); WriteSystemLog("housesjc/yhdetaile", pageData, pageRequest);

                return pageData;
            }
            catch (Exception ex)
            {
                var res = new RespRet<Page<object>>(false, ex.ToString());
                WriteSystemLogError("housesjc/yhdetaile", res, pageRequest);
                res.Msg = "系统异常";
                return res;
            }
        }











        /// <summary>
        /// 自查统计-为自查房屋下钻
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpPost("housesjc/wzcfw")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]

        public RespRet<object> wzcfw([FromBody] DateQuery param)
        {
            var usernNumber = this.CurrentUser();
            try
            {
                if (param == null || param.date == null || param.date.Count != 2)
                {
                    return new RespRet<object>(false, "请传递时间");
                }
                string sql = "";
                var user = _dbContext.ZmUser.FirstOrDefault(c => c.Number == usernNumber);
                var a = JsonRegion(user.District);
                JObject obj = JObject.Parse(user.District);
                string RegionCode = (string)obj["RegionCode"];
                RegionCode = RegionCode.Substring(RegionCode.Length - 3) == "000" ? RegionCode.Substring(0, RegionCode.Length - 3) : RegionCode;
                bool isxz = true;
                if (string.IsNullOrWhiteSpace(param.code))
                {
                    isxz = false;
                    param.code = RegionCode;
                }
                param.code = param.code.Substring(param.code.Length - 3) == "000" ? param.code.Substring(0, param.code.Length - 3) : param.code;


                param.date[1] = param.date[1].AddHours(23);
                param.date[1] = param.date[1].AddSeconds(59);
                param.date[1] = param.date[1].AddMinutes(59);
                // 假设 param.date[1] 已经被设置为 2025-06-14 23:59:59
                string formattedDate = param.date[1].ToString("yyyy-MM-dd HH:mm:ss");
                string table_name = "zm_building_fwzt_" + DateTime.Now.Year;


                if (param.code.Length == 3 || param.code.Length == 6)
                {
                    sql = @" select  xzqdm code,xzq name,wjccount wzccount from jcqkgqtext(@startime,@endtime::TIMESTAMP,@tablename) ORDER BY  xzqdm   ";
                }
                if (isxz)
                {
                    if (param.code.Length == 6)
                    {
                        sql = @" 
 select towndm code,town name,wjccount wzccount  from jcqkxztext(@startime,@endtime::TIMESTAMP,@code,@tablename) where towndm like @code ORDER BY  towndm  
    ";
                    }
                }
                if (param.code.Length == 9 || (param.code.Length == 12))
                {
                    sql = @" select villagedm code,village name,wjccount wzccount from jcqkcjtext(@startime,@endtime::TIMESTAMP,@code,@tablename) where villagedm like @code ORDER BY  villagedm    ";
                }
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql, new {@tablename=table_name, @code = "%" + param.code + "%", @startime = param.date[0], @endtime = formattedDate }).ToList();
                var res = new RespRet<object>(true, "Ok", data);
                WriteSystemLog("housesjc/wzcfw", res, null);
                return res;
            }
            catch (Exception ex)
            {
                var res = new RespRet<object>(false, ex.ToString());
                WriteSystemLogError("housesjc/wzcfw", res, null);
                res.Msg = "系统异常";
                return res;
            }
        }
    }
}
